7

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.

seegoon
  • 563
  • 1
  • 8
  • 15
  • 2
    This is just kind of a shot, but I don't know if it will help, but is the input workbook open on the other user's machines, because it has to be for the indirect function to work this. You cannot (at least w/o VBA, that I am aware of) update data from linked formulas without opening the workbook. Excel stores the link when you close the linked workbook, but since you are using indirect, it will not store a link, because in effect, you haven't made on. If the link where there, you would get prompted to update it and all would be well. If that doesn't work, we can look into other stuff. – Scott Holtzman Jun 21 '12 at 13:49
  • related: http://stackoverflow.com/questions/9259862/executeexcel4macro-to-get-value-from-closed-workbook and http://stackoverflow.com/questions/9033656/copying-from-closed-workbook-excel-vba – JMax Jun 21 '12 at 14:14
  • Thanks Scott, good point. The answer is yes, it is open. I have edited the question to reflect this detail. – seegoon Jun 21 '12 at 14:27

2 Answers2

2

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.

danielpiestrak
  • 5,279
  • 3
  • 30
  • 29
0

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.

James Chen
  • 237
  • 2
  • 6
  • 18