Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLOOKUP INDIRECT with alpha numeric worksheets
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLOOKUP INDIRECT with alpha numeric worksheets
#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 "Kelly" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLOOKUP INDIRECT with alpha numeric worksheets
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" wrote: #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 "Kelly" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLOOKUP INDIRECT with alpha numeric worksheets
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 "Kelly" wrote in message ... 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" wrote: #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 "Kelly" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HLookup indirect multiple worksheets | Excel Worksheet Functions | |||
Alpha Numeric tab not showing only numeric. | Excel Worksheet Functions | |||
only extract numeric value from alpha numeric cell | Excel Discussion (Misc queries) | |||
+1, Alpha *and* Numeric | Excel Worksheet Functions | |||
The colums changed from alpha to numeric how do you make it alpha | Excel Discussion (Misc queries) |