Assuming the location of the file is on cell D5 use the following formula
=INDIRECT("'"&D5&"Main'!$1:$65536") where D5 contains
'C:\Documents and Settings\xxx\My Documents\[my_spreadsheet2.xls]
"Markshnier" wrote in message
...
I have a formula in a workbook called my_spreadheet1.xls
=VLOOKUP(A1,'[my_spreadsheet2.xls]Main'!$1:$65536,27,FALSE)
i.e. In my_spreadsheet1.xls I have a formula that links to anoher
Workbook called my_spreadsheet2.xls.
My problem is that I want to have the name of the referenced Workbook
and the Worksheet within that Workbook be variable based on the values
in a couple of other cells.
For example if cell a2 = xxxxxxxxxx.xls, and cell a3 = yyyyyyy,
if would like to be able to create formula like
VLOOKUP(A4,'[xxxxxxxx.xls]yyyy'!$1:$65536,27,FALSE), where the xxxxx
and yyyyy are some function that will insert the contents of those
cells into the formula as text.
I have tried to use ADDRESS and INDIRECT where I have the xxxx and
yyyyy , but I can't figure ouit the syntax to make it work.
Any help would be appreciated.
Mark
--
Markshnier
------------------------------------------------------------------------
Markshnier's Profile:
http://www.excelforum.com/member.php...o&userid=16465
View this thread: http://www.excelforum.com/showthread...hreadid=278194