It's quite complicated, but I will do my best to explain it as clearly as possible. Please let me know if this makes no sense.
I have two books - entry and exit. They must be separate because of how the system works, i.e. The Nominee makes all the input into a simple tutorial for input, and the formatted workbook makes it ready to use. For this to work, the exit workbook must reference the input workbook to get the values.
I tested this by opening both workbooks.
For this, I use the nested functions INDIRECT ; the first creates a path to the file, calling the named range, and the second tells Excel to interpret this path to the file and retrieve the value.
I start using INDIRECT to create a file path:
=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 paste it into another to force Excel to read this path:
=INDIRECT("'"&INDIRECT("input_sheet_location")&"Wk 25 2012'!$B$12")
This successfully returns the value of cell B12 from input_sheet_location - the named range that is the file directory. For the argument, we can say that it returns:
Captain America underpants
So it works great. For me. However, it does not work on another user's computer. I tried to dig and developed the following:
- The connection between files is also present on their systems - a study of Data> Edit Links shows that they have the same working connection, like me.
- A file path occurs; I built a macro to show it to the user, and on three machines it exits the same every time.
- Most importantly (and vaguely), the non-nested
INDIRECT formula works. This is only a nested formula that only works on my computer. The error #REF returned on every other user's computer.
Can anyone understand why this could be so? I'm at a loss.
Thanks for reading this page.
seegoon
source share