CODEWITHSUNDEEP
excelreference
Arjan Oltwater
Arjan Oltwater

Reputation: 11

refer to a cell in another workbook which is named after the value in cell A1

In a destination workbook cell B1 has to return a value of cell C1 of another source workbook which is named after the value in cell A1 from the destination workbook. So for example if the cell value of cell A1 in the destination workbook is "source1" then cell B1 of should return the value of cell C1 in workbook "source1.xlsx" How should the formula in destination workbook cell B1 look like?

Upvotes: 1

Views: 52

Answers (2)

Guy Hodges
Guy Hodges

Reputation: 157

Rachel Hettinger's solution will work fine if the workbook is open. But INDIRECT doesn't work on closed books. If you want to do this for closed books you'll need Harlan Grove's PULL function:

http://numbermonger.com/2012/02/11/excel-pull-function-creating-dynamic-links-to-closed-workbooks/

Upvotes: 1

Rachel Hettinger
Rachel Hettinger

Reputation: 8442

The formula you need is INDIRECT. Try something like this:

=INDIRECT("'[" & A1 & "]Sheet1'!C1",TRUE).

This assumes the source sheet is Sheet1 and the name of the source workbook is stored in cell A1.

Upvotes: 1

Related Questions