Reference

[Guest]

I would like to make reference to a cell in a worksheet in a second excel
file. However, I would like the worksheet to be variable.
Normally you use the following link: '[Excelfile 2.xls]worksheet'!$c$2

The name of the worksheet to be used is a fixed value in the first file,
placed in cell A1. Is it possible to use a link like '[Excelfile
2.xls]Sheet($a$1)'!$c$2?

The part Sheet($a$1) does not work as a reference. Who can help?

 

[Jim Rech]

You can do something like this, but the second workbook has to be open.
INDIRECT will not work with a closed file.

=INDIRECT("[Book2.xls]"&A1&"!C2")

--
Jim Rech
Excel MVP
|I would like to make reference to a cell in a worksheet in a second excel
| file. However, I would like the worksheet to be variable.
| Normally you use the following link: '[Excelfile 2.xls]worksheet'!$c$2
|
| The name of the worksheet to be used is a fixed value in the first file,
| placed in cell A1. Is it possible to use a link like '[Excelfile
| 2.xls]Sheet($a$1)'!$c$2?
|
| The part Sheet($a$1) does not work as a reference. Who can help?
| --
| Thx

 

[Guest]

Thank you very much for your help in this. Only problem is the fact that the
2nd workbook needs to be open, otherwise it will show #Ref. So, if there
would be another solution where it is not necessary to open the 2nd workbook
(unless for updates or something like that)?