HLOOKUP INDIRECT with alpha numeric worksheets

[Guest]

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

 

[T. Valko]

#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

 

[Guest]

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

 

[T. Valko]

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