Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
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 '13 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 '13 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 '13 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 '13 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 '13 at 22:37
show 3 more comments

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.