Excel Using Reference for Worksheet names

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.

Paul

Posted 2013-12-05T21:17:39.883

Reputation: 1

Answers

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.

Netloh

Posted 2013-12-05T21:17:39.883

Reputation: 244

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 – 2013-12-05T22:03:25.790

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 – 2013-12-05T22:35:12.460

What does "Evaluate Formula" tell you? – Scott – 2013-12-06T03:20:27.977

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 – 2013-12-09T19:45:11.220

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.

nutsch

Posted 2013-12-05T21:17:39.883

Reputation: 1 923

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)

Firee

Posted 2013-12-05T21:17:39.883

Reputation: 1 694