COPYING FUNCTIONS

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a HLookup function that looks like this
=HLOOKUP($B$4,'C:\LJH\RCG\Accounting\2004\feb'' 04\[feb 04 financials.xls]consolidated YTD previous YR'!$N$111:$Y$207,2

I want to copy and paste this function into multiple rows. However, unlike copying other functions, the "2" at the end of this function does not automatically increment by one. If I copy this to 5 more rows, I have to manually go in and change the last digit on my Hlookup manually, i.e. 3,4,5,6,7.

Is there any way to programmically increment the last digit on the end of this Hlookup

Thanks.
 
Hi
if you start with this formula in row 2 (e.g. in cell B2) try
=HLOOKUP($B$4,'C:\LJH\RCG\Accounting\2004\feb'' 04\[feb 04
financials.xls]consolidated YTD previous YR'!$N$111:$Y$207,ROW())

and copy down. If you start in a different row replace
ROW()
with
ROW()-starting_row+2
 
Frank,

You would have to use the ROW() function in some manner to base
the value on the row in which the formula exists. E.g,

=HLOOKUP($B$4,'C:\LJH\RCG\Accounting\2004\feb'' 04\[feb 04
financials.xls]consolidated YTD previous YR'!$N$111:$Y$207,ROW())


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



FRANK said:
I have a HLookup function that looks like this:
=HLOOKUP($B$4,'C:\LJH\RCG\Accounting\2004\feb'' 04\[feb 04
Click to expand...
financials.xls]consolidated YTD previous YR'!$N$111:$Y$207,2)
I want to copy and paste this function into multiple rows.
Click to expand...
However, unlike copying other functions, the "2" at the end of
this function does not automatically increment by one. If I copy
this to 5 more rows, I have to manually go in and change the last
digit on my Hlookup manually, i.e. 3,4,5,6,7.
 
The original Hlookup function starts in cell J10. I want to copy this down the "J" column. Just using ROW() gives me the wrong value (where I originally had the "2") and trying the ROW()-starting_row+2 gives me errors in all the cell
What am I doing wrong??
Thanks.
 
Hi
you have to replace starting_row with the specific value. So in your
case use
=HLOOKUP($B$4,'C:\LJH\RCG\Accounting\2004\feb'' 04\[feb 04
financials.xls]consolidated YTD previous YR'!$N$111:$Y$207,ROW()-10+2)
 
What do you mean with 'screw-up'.
This just recalculates your formulas. If you receive some errors now
you have some real problems in your existing formulas. That is the
current values were outdated :-)
 
Gonna test your patience now. It works fine now but could you explain the Row()-10+2
What's going on there? Would like to understand what I'm using
Thanks again.
 
Back
Top