Cell content as a filename in a link ?

[Artur]

Hello,

I would like to use a given cell content as a filename in a link ?

In my workbook I have a cell A1 where I refer to external
spreadsheet in Book1.xls file:
A1='C:\My Documents\[Book1.xls]Sheet1'!G12

I wish to replace file name [Book1.xls] with the content of cell B1
so if I change it's content the linked file will adequately change
Ideally it should look like in below example but it doesn't work:
='C:\My Documents\[B1]Sheet1'!G12

Is it a matter of correct syntax or it needs a VBA programming ?

 

[Dave Peterson]

The function you'd want to use is called =indirect(). But the bad news is it
won't work with closed workbooks.

So if you have those other workbooks open, you can do it.

But Harlan Grove posted a function that opens another instance of excel and then
retrieves the value from that closed workbook.

http://google.com/[email protected]

Hello,

I would like to use a given cell content as a filename in a link ?

In my workbook I have a cell A1 where I refer to external
spreadsheet in Book1.xls file:
A1='C:\My Documents\[Book1.xls]Sheet1'!G12

I wish to replace file name [Book1.xls] with the content of cell B1
so if I change it's content the linked file will adequately change
Ideally it should look like in below example but it doesn't work:
='C:\My Documents\[B1]Sheet1'!G12

Is it a matter of correct syntax or it needs a VBA programming ?