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 two spreadsheat files (D:\Book1.xlsx and D:\Book2.xlsx). The first workbook has a table, Table1 with three columns (id, Name, Value). In the second work book I to want reference values in Table1 using VLOOKUP. My current formula for referencing values is

=VLOOKUP(1,'D:\Book1.xlsx'!Table1[#Data],3)

Which works when both workbooks are open. However, if I open Book2.xlsx by itself the above formula evaluates to REF#! and the formula has an absolute path instead of the relative path.

As soon as I open Book1.xlsx the path reference becomes relative and the formula evaluates to the correct value.

So I was wondering how do I get the external reference to work without having to open both workbooks (or is this possible)?

Note

  1. To be clear my formula returns #REF! when the second workbook is opened by itself.

  2. Both workbooks reside on the root of my D partition as d:\Book1.xlsx and d:\Book2.xlsx.

share|improve this question
    
just saw this behavior yesterday myself –  warren Jan 27 '10 at 22:24
    
Personally, I have never dealt with this before and can't recreate. Could it be related to absoloute paths? Can you put the actual path in the code e.g. c:\temp\book1.xlsx ? –  William Hilsum Jan 28 '10 at 16:05
    
@Wil I put the actual path into the formula and it still only works with when both files are opened. –  Azim Jan 28 '10 at 22:28

3 Answers 3

I have had this problem before, but while recreating your example everything was working fine, so there had to be something. I think I may have found it:

When selecting external ranges, Excel sometimes has a tendency to revert them to huge references like this:

=VLOOKUP(1,[Workbook1.xlsx]Sheet1!$A$4:[Workbook1.xlsx]Sheet1!$A$10,1,TRUE)

This works fine while 2 workbooks are open but really there's no need to specify the source both times in the range, so it could very well be shortened to this:

=VLOOKUP(1,[Workbook1.xlsx]Sheet1!$A$1:$A$10,1,TRUE)

Even better, you can also use named ranges:

=VLOOKUP(1,[Workbook1.xlsx]Sheet1!MyRange,1,TRUE)

Surprise, surprise! If Workbook2 is opened alone, the long form shows #REF, while the shorter version and the named range version update neatly.

share|improve this answer
    
strange. I cannot seem to use the file reference with the brackets around the filename instead of the single quotes (ticks). –  Azim Jan 28 '10 at 22:40
    
That might actually related to regional settings or something. Have you tried using a named range? –  mtone Jan 29 '10 at 2:04

I read somewhere that functions that require a range, e.g., VLOOKUP(), will return #REF when the source file is closed. I was able to work around by adding a tab in the destination file and replicating the source table through a direct reference to the source table. Then I reference this replicated table In the vlookup formula (which is now in the same workbook)and all worked well.

share|improve this answer

This looks to me like a bug in Excel. I can reproduce the behavior as well, but I have all options related to external data enabled and valid. This includes all options in the Trust Center Settings and all workbook references listed in External Links. Manually updating values from External Links doesn't do it, either.

I've also followed the instructions in this MS Support article. It works to an extent, but everything reverts to the behavior you note after I close.

share|improve this answer
    
So, what your saying is this is a bug in excel and there is no work around?! –  Azim Jan 28 '10 at 22:41

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.