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

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