Is it possible to use a cell value as a workbook/worksheet reference in an excel formula?

1
votes

Let's say I have a worksheet called "References". The value of cell B2 of this worksheet is
"C:\Documents\Workbook1.xlsx",
the path to a workbook.

On another sheet within the same workbook I want a formula which gets the value of cell A1 of worksheet "Sheet 1" of Workbook1.xlsx. Is it possible to have the formula look up the value of 'References'!B2 and use it as a reference.

i.e. I'd expect the formula would be something like

='['References'!B2]Sheet 1'!A1

I have tried using the CELL() formula but can't seem to get it to work in this way.

2
excelvba

2 Answers

1
votes

As this is a variable reference I presume that your source workbook is likely to be closed.

Attached is a summary (and screenshot in case the link ever changes) from Daily Dose of Excel covering how to pull a variable reference from a closed workbook . See INDIRECT and closed workbooks

Three options are provided:

  1. Laurent Longre has developed the free add-in MOREFUNC.XLL which includes the function INDIRECT.EXT
  2. SQL.REQUEST
  3. Harlan Grove’s PULL function:

enter image description here

1
votes

Assuming the referenced workbook is open, you can use the INDIRECT() function.