Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#REF! error with Multiple Documents
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) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#REF! error with Multiple Documents
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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#REF! error with Multiple Documents
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) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#REF! error with Multiple Documents
@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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#REF! error with Multiple Documents
[#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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#REF! error with Multiple Documents
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" 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#REF! error with Multiple Documents
@ 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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#REF! error with Multiple Documents
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Blank Rows Code - Multiple Worksheets - Multiple Documents | Excel Discussion (Misc queries) | |||
Multiple documents open, but only seeing one | Excel Discussion (Misc queries) | |||
how do I converting multiple documents into one? | Excel Discussion (Misc queries) | |||
Error: Cannot open two documents with the same name | Excel Discussion (Misc queries) | |||
Error prompt when opening Excel documents. | Excel Discussion (Misc queries) |