VLOOKUP with INDIRECT cell references is the answer to your question,
but it will only work if the sheets are open. You can't INDIRECTLY
reference sheets that are closed. You can directly reference them, so if
you need to do that, you'll have to manually create the references.

If you're trying to display the information in cell A1 from the sheet
Losses12.xls, and the number 12 was in the cell B5 on the current sheet,
the INDIRECT formula would be:

=INDIRECT(\"[LOSSES\"&B5&\".XLS]SHEET1!A1\")
[/b]
IF YOU HAD A SIMPLE VLOOKUP:
*=VLOOKUP(_1_, A1:B20,2,FALSE)
...and wanted to insert the INDIRECT formula so that the lookup value
came from the other sheet, just insert it into the underlined field:

[b]=VLOOKUP(*_*INDIRECT(\"[Losses\"&B5&\".xls]Sheet1!A1\")*_*,
A1:B20,2,false)*


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=45493