0

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

Here's my line of code,

='[WorkBook]BOM'!G6

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)

='[=E1]BOM'!G6

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.

Improve this question
0

3 Answers 3

Reset to default
0

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.

Improve this answer
4
  • 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, 2013 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, 2013 at 22:35
  • What does "Evaluate Formula" tell you?
    – Scott - Слава Україні
    Dec 6, 2013 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, 2013 at 19:45
0

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.

Improve this answer
0

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)

Improve this answer

Not the answer you're looking for? Browse other questions tagged .