why doesn't this INDIRECT resolve correctly?

[hank_t]

In cell E1 I have the value "C38U.SI" - no qoutes; this is a stock code.

I have a worksheet with the same name (I have used VBA to label the sheet and renamed it manually to see if there was an embedded character in the name) in the same workbook

In another cell, I lookup the CLOSE PRICE with the following:
=IFERROR(VLOOKUP($A24,INDIRECT($E$1&"!B2:H9000"),7,FALSE),0)
[note, on the sheet named C38U.SI there is valid date with DATE as the first field (B2) and CLOSE PRICE in the 7th column; A24 is a date on the worksheet requesting the CLOSE PRICE]

The above always returns 0 for this stock only.

This exact formula is in every other column underneath every other stock code (which all have a corresponding worksheet in the current workbook) and resolves to the value in the 7th column (CLOSE PRICICE) of the stock code's work sheet for the correct date in cell A24.

If I rename the spreadhseet from C38U.SI to "mmmm" (without the quotes) and I put "mmmm" (without the quote) as the column header for the stock C38U.SI on the sheet where that indirect is resolved, THE LOOKUP WORKS LIKE ALL OTHER TICKER LOOKUP FUNCTIONS!!!!

[protonLeah]

You need to enclose the tabname in single quotes:

Please Login or Register  to view this content.

[protonLeah]

Testing reply count in this thread.