CODEWITHSUNDEEP
excelexcel-formula
seegoon
seegoon

Reputation: 573

Nested INDIRECT functions in Excel--working on one computer but returning #REF on another

This is pretty complex, but I'll do my best to explain it as clearly as possible. Please let me know if it doesn't make sense.

I have two workbooks--an input and an output. They have to be separate because of the way the system works, i.e. a 'nominator' does all the inputting into a simple input workbook and the output workbook formats it ready for use. In order for this to work, the output workbook has to refer to the input workbook to retrieve values.

I have been testing this with both workbooks being open.

To achieve this, I make use of nested INDIRECT functions; the first one creates the file path, calling upon a named range, and the second one tells Excel to interpret that file path and retrieve the value.

I start off by using INDIRECT to build a filepath:

=INDIRECT("input_sheet_location")&"Wk 25 2012'!$B$11"

This returns something like:

\\My Documents\Subfolder\[input_sheet.xlsx]Wk 25 2012'!$B$11

And then nest it in another in order to get Excel to read that path:

=INDIRECT("'"&INDIRECT("input_sheet_location")&"Wk 25 2012'!$B$12")

This successfully returns the value of cell B12 from the input_sheet_location--a named range, which is a file directory. For argument's sake we can say it returns:

Captain America's underpants

So the above works perfectly well. For me. However, on another user's machine, it doesn't function. I have tried to dig in, and worked out the following:

  1. The connection between the files is present on their systems too--exploring Data>Edit Links shows they have the same, working connection as I do.
  2. The file path produced is the same; I built a macro to show it to the user, and on 3 machines, it's come out the same every time.
  3. Most crucially (and confusingly), the non-nested INDIRECT formula does work. It is only the nested formula which only works on my computer. On every other user's computer, it returns a #REF error.

Does anyone have any idea why this might be the case? I am at a loss.

Thanks for reading that spiel.

Upvotes: 7

Views: 5391

Answers (2)

James Chen
James Chen

Reputation: 237

might be a dumb question but does this actually work?

=INDIRECT("input_sheet_location")&"Wk 25 2012'!$B$11"

doesn't the parenthesis at the end of _Location close the Indirect function? shouldn't it be

=INDIRECT("input_sheet_location"&"Wk 25 2012'!$B$11")

At least that's the only way it works for me is to have the whole path inside the Indirect function parenthesis.

Upvotes: 0

danielpiestrak
danielpiestrak

Reputation: 5439

You said the first Indirect formula + Concatination returns a value like:

\\My Documents\Subfolder\[input_sheet.xlsx]Wk 25 2012'!$B$11

Shouldn't the sheet name have a single quote on each side and return a value like:

\\My Documents\Subfolder\[input_sheet.xlsx]'Wk 25 2012'!$B$11

I notice your second formula has the single quote for the sheet name before the file path.

Instead try the following two formulas:

=INDIRECT("input_sheet_location")&"'Wk 25 2012'!$B$11"

And/or

=INDIRECT(INDIRECT("input_sheet_location")&"'Wk 25 2012'!$B$12")

Let me know if they work for you.

Upvotes: 2

Related Questions