On May 30, 7:11 pm, Kelly wrote:
I am using the following macro to gather the information that I need:
=HLOOKUP($A380,INDIRECT("'"&$A380&"'!D2:H104"),29, FALSE)

When I change the formula to
=HLOOKUP($A380,INDIRECT("'"&$A380&"'!F2:H104"),29, FALSE)
to gather data from the 3rd column of the data area, I get a #NA

I made sure to copy the lease number (the A380 reference) to each column
(D2, F2, H2) on the worksheet I'm looking up.

Can you please tell me what's wrong with the second macro? And why I'm
getting the #NA response?

thanks
Kelly

Kelly,
your formula seeks the sheet named in A380 and then goes to the range
D2:H104 and does the HLOOKUP there to find what is in row 30 of the
column that has A380's value in row 2. HLOOKUP (and VLOOKUP) require
the lookup value to appear in the first row (or column respectively)
of the lookup table. If you change your reference to F2:H104 HLOOKUP
will not be able to find the lookup value.

HTH
Kostis Vezerides