5

I have a reference to another workbook that looks like this:

 =INDIRECT(ADDRESS(21,6,1,1,"[Longterm Budget.xlsx]2012"))

The result of that INDIRECT argument, ADDRESS(21,$A$1,1,1,"[Longterm Budget.xlsx]2012"), is '[Longterm Budget.xlsx]2012'!$F$21. If I put an equal sign in front of that

 ='[Longterm Budget.xlsx]2012'!$F$21

...it returns the desired reference. However, passing that same argument to INDIRECT like this

 =INDIRECT("'[Longterm Budget.xlsx]2012'!$F$21")

...results in a reference error. Any idea why?

Improve this question
8
  • Apparently it has something to do with whether I have the externally referenced workbook opened at the same time. I'm not sure why the INDIRECT operator needs both opened simultaneously when the "=" operator does not.
    – todorojo
    Jan 14, 2013 at 8:39
  • Peter has explained why INDIRECT gives you an error.......but can you explain why you are using INDIRECT here? I'd say you only need it if you want the reference to the workbook to be dynamic......
    – barry houdini
    Jan 14, 2013 at 11:41
  • The other notebook is arranged in a way that a direct reference would take a lot of hand-coding. Instead, I use ADDRESS to calculate the reference. My actual address argument is a bit more complicated than I've presented it here, but since I managed to identify the problem as a difference between the "=" operator and the INDIRECT operator, I simplified a bit for clarity.
    – todorojo
    Jan 14, 2013 at 18:14
  • 1
    OK but then you could probably use INDEX function instead of INDIRECT which should work with a closed workbook, i.e. =INDEX('[Longterm Budget.xlsx]2012'!$A1$Z100,21,6) where 21 and 6 are the row and column references from your ADDRESS function. I made the range $A1$Z100 so that will work within that range, change if it needs to be larger
    – barry houdini
    Jan 14, 2013 at 18:32
  • 1
    I'm not sure why INDEX won't work - the big advantage here is that INDEX will work with a closed workbook (unlike INDIRECT or OFFSET). Although it's generally better to use INDEX anyway, even if the workbook isn't closed - with INDEX you can directly reference the cell you want rather than using ADDRESS to create a text string and then INDIRECT to convert back to a reference
    – barry houdini
    Jan 14, 2013 at 22:37

2 Answers 2

Reset to default
10

INDIRECT unfortunately only works with within the workbook or with workbooks that are open during the calculation time. If the external workbook is closed, it will result in a #REF! error.

Background: For normal external links, Excel stores not only the address to the external cell, but also the last known value in the saved version of the file. This way, when opening the file, Excel can calculate the the whole workbook, even if the external source is not available.

INDIRECT however is a volatile function, i.e. Excel has to calculate it every time a calculation is run (while a "normal" function only has to be recalculated if any of the predecessor cells change). Therefore, Excel cannot store the value of the reference and thus results the #REF! error.

Improve this answer
-1

OK, I just posted this on StackOverflow... I hope this not double-posting...

Here's a dinosaur method for you on Office 2010.

Write the full address you want using concatenate (the "&" method of combining text).

Do this for all the addresses you need. It should look like:

="="&"'\FULL NETWORK ADDRESS including [Spreadsheet Name]"&W3&"'!$w4"

The W3 is a dynamic reference to what sheet I am using, the W4 is the cell I want to get from the sheet.

Once you have this, start up a macro recording session. Copy the cell and paste it into another. I pasted it into a merged cell and it gave me the classic "Same size" error. But one thing it did was paste the resulting text from my concatenate (including that extra "=").

Copy over however many you did this for. Then, go into each pasted cell, select he text and just hit enter. It updates it to an active direct reference.

Once you have finished, put the cursor somewhere nice and stop the macro. Assign it to a button and you are done.

It is a bit of a PITA to do this the first time, but once you have done it, you have just made the square peg fit that daamned round hole.

Improve this answer

You must log in to answer this question.

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