I've looked around to try and resolve this, however I am unfamiliar with the coding.

Here's my line of code,


What I want to do is make the Workbook call to a cell so I can mass replace all the 'Workbook' names without Find and Replace.

Essentially, something like this (Though this code does not work, it is just for reference)


Then in cell E1, I would be able to enter any text I want, to replace the Workbooks name. I'm trying to find the Formula to do this.


You should use the INDIRECT() function to evaluate a text string as a function.

=INDIRECT("'["& E1 & "]BOM'!G6")

Note that the INDIRECT() function will only return the value if the referenced workbook is open. Otherwise it will return a #REF!-error.

  • I tried implementing this code into my excel sheet, as did one of my co-workers into theirs. Neither of us received results, as we both came up with #REF! The WorkBook I'm referencing is open as well. Thanks for the fast response, however. – Paul Dec 5 '13 at 22:03
  • As noted, this might be because the workbook you are referencing to are closed. This is the closest you would come such a solution without using VBA. – Netloh Dec 5 '13 at 22:35
  • What does "Evaluate Formula" tell you? – Scott Dec 6 '13 at 3:20
  • note also that it will expect the full name - workbook would not work, you would have to put workbook.xlsx - If the file is closed, you would need the full pathname: C:\Users\MyName\Documents\workbook.xlsx – SeanC Dec 9 '13 at 19:45

INDIRECT will not work with closed outside workbooks. Your best bet at automation seems to be to put the workbook path in a cell, and write a macro that will find/replace all the occurrences of the old workbook name and replace with the new workbook name.


My solution would let you reference a range, only if you name them first.

For example you name the range A1:A100 as MyRange in Workbook 1

Then in your current Workbook you can enter "MyRange" in E1 in Workbook 2

then use INDIRECT(E1)

Not the answer you're looking for? Browse other questions tagged or ask your own question.