#REF! error with Multiple Documents

[zxcvbnm6000]

I keep receiving a #REF! error when I try to use VLOOKUP to find data in one
document and bring it to another. If both documents are open all the numbers
are fine. However when I close the book with the data I receive the error, or
when the vlookup book is opened it does it. If tell it to update the data
error, if I don't update error. What is weird is I have other documents that
work fine, with the same equation. Please help because I'm lost on this
issue. Happens Excel 2007 and 2010.

=VLOOKUP(A2,'C:\Users\USER\Documents\BOOK DATA.xlsx'!Data[#All],2,FALSE)

 

[Bob Bridges]

Someone else will be able to answer this more definitively, z, but what I can
add is that I've seen the same problem. I concluded at the time that it
depended on which servers the target workbook was on; if it was a shared
server, it wouldn't work, but if it was my own hard drive I had no
difficulty. If you can either confirm that finding or knock it in the head,
let me know; I'm still figuring out what causes it.

 

[JLatham]

Please double check the formula you posted, perhaps copy it exactly from the
workbook in question. I don't see a sheet name or valid name in the formula
you put up. Data[#All] is not a valid Name, and [] are not valid characters
in a worksheet name. At least I can't get a name defined as Data[#All] in
Excel 2007 (nor in 2003).

 

[zxcvbnm6000]

@Bob Bridges - It is on the same PC and same HDD.

@ JLatham - I copied the equation correctly. Data[#All] refers to the table
"Data" and [#All] indicates that it includes the whole table not just pieces.
I found it odd too, that there is no sheet name, but if I try to enter it,
Excel just updates the equation to what I pasted.

 

[JLatham]

[#All] is a feature I haven't played with and am unfamiliar with, sorry. But
I think since by implication you're interested in the entire table 'Data',
have you tried deleting the [#All] portion of the formula to see how things
work then?

 

[JLatham]

OK, late note and not sure it adds any real value for you or not. I see that
[#All] is created as a reference to a Table defined rather than a normal
named range. That explains my confusion (old school <g>). So I was able to
set up a formula just like yours and it works whether the DATA BOOK is stored
on a local drive or one of my network drives, and whether or not that book is
open.

But one thing I found was that if I edited the formula and removed the
[#All] portion, that I got a #REF error, and just typing it back in did not
clear up the #REF error until I re-opened the DATA BOOK, at which time it
would figure things out again. So perhaps opening up DATA BOOK and
re-entering the formula may help?

 

[zxcvbnm6000]

@ JLatham - Removing the [#All] will not work, as you found out, since it is
the key reference to what part of the table to use. The thing is you wrote
that when you opened up the DATA BOOK again the #REF! errors go away, which
is true. But if you close it again, save the book with the VLOOKUP equations,
close, then reopen, the errors are right back again. For some reason the data
will not save and will not update from the DATA BOOK without having it open.
It is just weird.

I do have another book where I use VLOOKUP that is updating fine, so I'm
going to study the equation to see where the difference is. Hopefully I can
figure something out.

 

[zxcvbnm6000]

So I looked into this more and found if I use the sheet name and the A1:C10
style reference it works, but using a table name reference doesn't. Is this a
glitch or something? I can't figure out why these two things aren't the exact
same thing.

Also to completely confuse me, I found the table name reference works
sometimes when embedded in a certain equations, but not all of them.

If someone can explain this to me I'd love to hear it.