How do I use a cell as a referance to a file in a lookup statemen.

[Guest]

I have a file that is a summary. I want to look up data in another file
using a cell in the summary file to define part of the file name. For
example: Cell A5 contains 2008. I want to have a formula like
=VLOOKUP(B4,'H:\Sarah P\CQI or PIP\Acute Inventory\Test
Process\[2008.xls]count_sheet'!$B:$E,4,FALSE) I want to use the cell
referance of A5 to represent the number (2008) in the formula.

 

[Harlan Grove]

SarahP wrote...

I have a file that is a summary. I want to look up data in another file
using a cell in the summary file to define part of the file name. For

example: Cell A5 contains 2008. I want to have a formula like
=VLOOKUP(B4,'H:\Sarah P\CQI or PIP\Acute Inventory\Test
Process\[2008.xls]count_sheet'!$B:$E,4,FALSE) I want to use the cell
referance of A5 to represent the number (2008) in the formula.

If the other file would be open, you can use INDIRECT.

=VLOOKUP(B4,INDIRECT("'["&A5&".xls]count_sheet'!B:E"),4,0)

If the other file wouldn't be open, you'll need alternative #4 in

http://groups-beta.google.com/group/microsoft.public.excel.worksheet.functions/msg/ac443753560f0075
(or http://makeashorterlink.com/?F2993260A )