#REF! error with Multiple Documents
in [Functions]
|
From: zxcvbnm6000 on 30 Jan 2010 18:10 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)
From: Bob Bridges on 31 Jan 2010 01:14 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. --- "zxcvbnm6000" wrote: > 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)
From: JLatham on 31 Jan 2010 01:15 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" wrote: > 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)
From: zxcvbnm6000 on 31 Jan 2010 03:25 @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.
From: JLatham on 31 Jan 2010 10:03
[#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? "zxcvbnm6000" wrote: > @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. |