Search code examples
excelexcel-2010excel-2013

Reference the value of another spreadsheet file

I'm triying to reference the value of a cell that is in another spreadsheet file. I know that I can do by using

=[file.xlsx]Worksheet!A1

But the file name is hardcoded in the formula and what I want is to reference it as a named cell. So suppose I define B1 as a named cell called "FileName". Then I try:

=[FileName]Worksheet!A1

I get a #REF error.

How can I use the value of a named cell to reference another file?

Solution

  • You have to use INDIRECT. 

    =INDIRECT("'[" &B2 & "]worksheet'!$A$1")

    ..then replace the worksheet name.