HLOOKUP INDIRECT with alpha numeric worksheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kelly - 04 Jun 2007 23:01 GMT

Thanks to the help from a prior posting, I have the following calculation in
my spreadsheet:
=HLOOKUP(A5,INDIRECT("'"&$A5&"'!D2:H104"),3,FALSE)
This macro searches through several worksheets with lease numbers (e.g.
003403, 021456) to produce the results.
When I have a lease number that includes a letter (e.g. 003403R, 045789A), I
receive an #N/A response.
Can you please tell me where I've gone wrong and/or what I need to change in
my formula?
Thanks
Kelly

Reply to this Message

T. Valko - 05 Jun 2007 05:37 GMT

#N/A means not available. This happens when the formula can't find the
lookup_value. Are you sure the lookup_value is present in the lookup_table?

It looks like your lookup_value is also the sheet name. If the sheet name
didn't exist Excel would open a dialog box asking you to select a file that
does contain that sheet name. I guess that doesn't happen?

Biff

> Thanks to the help from a prior posting, I have the following calculation
> in
[quoted text clipped - 10 lines]
> Thanks
> Kelly

Reply to this Message

Kelly - 05 Jun 2007 14:08 GMT

Both my summary sheet (where the
=HLOOKUP(A5,INDIRECT("'"&$A5&"'!D2:H104"),3,FALSE) command exists) and the
related worksheet have the lease number 004303R. Where all of the other
leases that do not include letters have accurately picked up the data from
the attached spreadsheets, the leases that include a number consistently
return the #N/A.
Thanks
Kelly

> #N/A means not available. This happens when the formula can't find the
> lookup_value. Are you sure the lookup_value is present in the lookup_table?
[quoted text clipped - 19 lines]
> > Thanks
> > Kelly

Reply to this Message

T. Valko - 05 Jun 2007 18:56 GMT

Hard to say why it isn't being recognized. Leading/trailing spaces? I'll
take a look at it if you want to send a copy of the file to me. This sounds
like a big file. If you want to send a copy and it's over 1mb, zip it. I'm
at:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

> Both my summary sheet (where the
> =HLOOKUP(A5,INDIRECT("'"&$A5&"'!D2:H104"),3,FALSE) command exists) and the
[quoted text clipped - 33 lines]
>> > Thanks
>> > Kelly

Reply to this Message