#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)
0
Utf
1/30/2010 11:10:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

7 Replies
899 Views

Similar Articles

[PageSpeed] 48

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)
0
Utf
1/31/2010 6:14:01 AM
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)
0
Utf
1/31/2010 6:15:01 AM
@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.
0
Utf
1/31/2010 8:25:01 AM
[#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.
0
Utf
1/31/2010 3:03:01 PM
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.
0
Utf
1/31/2010 3:18:01 PM
@ 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.
0
Utf
1/31/2010 8:50:02 PM
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.
0
Utf
2/5/2010 2:25:01 AM
Reply:

Similar Artilces:

Strange error message.
Visual C++ Version 6 running on XP Professional. I am running my program in debug mode. On one particular computer the following error appears at a random interval of many minutes after program start: The program runs fine in debug mode on other computers. Error box: User breakpoint called from code at 0x7c901230 The stack display is: NTDLL! 7c901230() NTDLL! 7c96cd80() NTDLL! 7c96e225() NTDLL! 7c94bbfb() KERNEL32! 7c838f70() WDMAUD! 72d222ce() WDMAUD! 72d24617() KERNEL32! 7c80b50b() The debug window shows several identical instances of the following line. These messages are intersper...

E-mail error
Whenever a client tries to send an e-mail from the web client (whether from internet explorer or from within Outlook Client but not using Outlook, trying to mail from a record in CRM) they get the following error: MSCRM Platform Error Report: ---------------------------------------------------------------------------- ---------------------------- Error: <description>An unexpected error occurred.</description><details>An error occurred attempting to dispatch the email : HTTP status: 404 Not Found. URL may be invalid</details><file>D:\crm\Build\3297\src\platform...

Error 3051 Help Please
I have developed an Access 2007 database (split into fe/be) and have access set to shared and no locks. When I place the BE on the server and try to connect the FE (on user PC) linked tables it is resulting in a error 3051. I have opened 5 differnet versions of the FE on my PC (FE and BE on the pc) without any issues. Is this error most likely the result of permissions on the server or is there something else I need to look at? Any hints/tips would be appreciated. SK, First things ti look at... Make sure you are not opening the database in Exclusive mode and that it is ...

Error message #8
I have purchased a program in excel and I am getting the followin message when I use the macro:- Runtime Error �1004� unable to set the printquality property of th pagesetup class The programmer has not got back to me yet other than to advise me t lower my sercurity settings in macros which has not helped. Does anyone have any idea what I can do. Thanks.:rolleyes -- allipop ----------------------------------------------------------------------- allipops's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3445 View this thread: http://www.excelforum.com/showthread...

Please HELP Error while upgrading
I the following error while upgrading : The index 'ndx_entity_name' is dependent on column 'Name' Thx, Ronen A bit more data: Here is the information from the log file: 18:17:29| Info| Executing Install action: Microsoft.Crm.Setup.Server.ExportXmlAction 18:17:35| Info| Executing Install action: Microsoft.Crm.Setup.Server.InstallDatabaseAction 18:17:36| Info| Dropping Microsoft CRM 1.0 reports database 18:17:36| Info| Dropping merge publication in METABASE 18:17:36| Info| Drop merge publication in MSCRM database 18:17:36| Info| Upgrading metadata schema to a...

Createmailbox error C1032227 "Object is of the wrong class"
Attempting to create an Exchange 2003 mailbox using ASP, with domain admin account, I've got this error message : "The object is of the wrong class error id=3Dc1032227 Microsoft CDO for Exchange Management -2147024809" Technical environment: Exchange 2003 v6.5.7226.0 Windows 2003 Server Standard Edition SP1 IIS 6 Native 2003 Active Directory environment This is my code: Set objuser =3D GetObject("LDAP://xx.yy.com/CN=3Dusername,CN=3DUsers,DC=3Dxx,DC=3Dyy,DC=3Dc= om") Set objMailbox =3D objUser 'IMailboxStore interface objMailbox.CreateMailbox "LDAP://CN=3DBanq...

text boxes removed once document is completed
how do I remove the text boxes so they won't show. I need to email the document and the text boxes are yet appearing on document. I don't want them to show once I have completed the document. Dominion wrote: > how do I remove the text boxes so they won't show. I need to email the > document and the text boxes are yet appearing on document. I don't > want them to show once I have completed the document. ============================= Try typing...Ctrl+Shift+O -- John Inzer Digital Media MVP Notice This is not tech support I am a volunteer ...

Error -19799
Hello: I have a user connecting to an exchange server using Entourage 2008 EWS version Mac Os 10.6.3 / Intel Mac Book Pro. Everything's been fine until this morning when he started getting -19799 errors whenever he tired to send an email. He's connected to the Exchange server and can receive mail but get's this message when he composes a message. I've searched for this error message but have come up empty. The Entourage site lists error messages but skips over this #. What's up with that? Why not list all the possible error messages. On 4/7/10 9:41 AM, Ric...

how do i sync multiple copies of the same excel file?
Looking for a way to sincronize multiple copies of the same Excel file automaticly. Each time one is changes, copy it over all the others. (use VBA code). -- Regards, Tom Ogilvy "graphicdoug" <graphicdoug@discussions.microsoft.com> wrote in message news:5D48C804-EC45-4F5E-BC77-DBBF295177A7@microsoft.com... > Looking for a way to sincronize multiple copies of the same Excel file > automaticly. ...

Memory Write Error on Exiting Outlook Express
Receive new mail in OE6. View, action mail OK. On exiting OE6 get, on a frequent basis: MSIMN.EXE Application Error. the instruction at "0x77fcb3ad" feferenced memory at "0x00ca45b0" - the memory could not be "written". Take the OK option oferred and program ens OK. Obviously dependant on whats running at the time the memory referenced changes. Only get this problem in OE6. Thanks Ken Hi, Ken - This is a group to support Outlook from the Office group of programs. Outlook Express is a part of Internet Explorer and is a quite different program, despite its sim...

#Ref! in cells of row linked to deleted row
Does one just delete the row? Why wasn't it just deleted like the row i the database since it was linked to it? Thanks, Trud -- lburg80 ----------------------------------------------------------------------- lburg801's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2833 View this thread: http://www.excelforum.com/showthread.php?threadid=48026 ...

Multiple Recipients Handling
With an email message that has multiple recipients in the header, can I configure Exchange to break-up that header up so that only 1 recipient is in the message before delivery? For example, if I send an email message to person1@somewhere.com and person2@nowwhere.org, can I have Exchange break that up into 2 separate e-mail messages before sending? Thanks. Hi, ShootMePlease wrote: > With an email message that has multiple recipients in the header, can > I configure Exchange to break-up that header up so that only 1 > recipient is in the message before delivery? > > Fo...

Internal error occurred on the report server
When I run a report in CRM, sometimes I receive the error "An internal error occurred on the report server" and the SQL Dumper shows the error "AdjustTokenPrivileges () failed (0000514). When I refresh the report, sometimes the report is generated. I have SQL Server 2005 SP 2 installed. Does anyone have the same issue? ...

Receivables Error Messages
I have the following error messages; can anyone help? 1. An open operation on table 'GL_DP_Distribution_Temp'failed because the path does not exist. 2. Unhandled database exception; A save operation on table 'ASI_SQL_Info' has caused an unknown error (48) Hi Tony, We received #1 error today for the first time. Looking forward to resolution. "Tony F" wrote: > I have the following error messages; can anyone help? > > 1. An open operation on table 'GL_DP_Distribution_Temp'failed because the > path does not exist. > > 2. Unhandled ...

Supported Version Error?
New 3.0 client installs running into the following error. "Setup failed to determine whether a supported version of Microsoft Outlook is installed" Outlook 2003 has been removed and reinstalled multiple times. No other errors. Office updates applied. This is happening on multiple machines in both the desktop and laptop version. It happens direct from the CD and also using the SBS application installer. Any suggestions for getting past this would be appreciated. John Located a post referencing installing Office SP1 and updating the Windows installer. Applied with no effe...

Error 0x80ccc0f
This is becoming a real PIA! I can be using Outlook for hours or only minutes and get this error. When I shut the program down and re-start it, it again works for hrs or minutes. Nothing I do seems to make a difference one way or another. Anyone have a suggestion? Thanks, Larry Outlook 2000 SP-3 Ver. 9.0.0.8954 Webmail Accounts Google Yahoo Mail Plus (default) OS Name Microsoft Windows 2000 Professional Version 5.0.2195 Service Pack 4 Build 2195. Submitted using http://www.outlookforums.com Are you using a Linksys router? Linksys routers have a history of problems with ma...

Class does not support automation error
When launching Store Ops Mgr, I get the error, "class does not support automation or expected interface". Knowlegdge Base says the cause could be Epson Opos drivers installed and registered incorrectly. I have no Epson Opos drivers installed. I did have a Star Opos driver installed and I uninstalled/reinstalled it, but had no option for registering a certain way. Any other suggestions would be appreciated. Thanks, Ann Pack Advanced Retail Management Systems Reload Store Ops, you have overwritten some files with the OPOS. Back up first! mte "Annie" <Annie@d...

Get error message when press Test account setting
I just set up a new POP account on my Outlook and i get this message everytime i press the Test Account settings button. Send test email message: Unable to send test Message. Please verify the E-mail Address field. I still can send and receiev mail but this error is really bugging me. ...

Error 9582 on Exch2000SP3 and W2KSP4 w/2.5 G RAM
HELP!!!! I have a single Exchange server with 2.5G RAM. Exchange 2000 SP3 Windows 2000 Server SP4 I am getting a 9582 *warning* message: The Virtual memory necessary to run your Exchange server is fragmented in such a way that performance may be affected. It is highly recommended that you restart all Exchange services to correct this issue. I have gone to EventID.net and followed along with all the Qxxxxx articles. I have: 1. Installed the last Post-SP3 patch (Store.exe shows version 6.0.6603.0) 2. I have added the HeapDecommit reg entry detailed in Q315407 3. I have set up counters in Pe...

CANNOT open document in Word 97
Was always able to open a .doc, but suddenly, I get: "Word cannot open the document...Try File, Open or ensure has .doc extension." How can I check that it has a .doc extension? And why would this suddenly occur? From where did you obtain the document that you cannot open? How are you trying to open it? -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><&...

Target Machine Error
Hey everyone, i have speant weeks creating a program in vc++ 2005 MFC, and it has come time to move it onto the target machine. When i move it onto the target machine, and try to execute it, it displays the message "this application has failed to run because it contains errors". When i run 'depends.exe' on this executable, it reports the following is wrong: Error: The Side-by-Side configuration information in "c:\documents and settings\g853ahe\desktop\DBCH MANAGEMENT SYSTEM.EXE" contains errors. This application has failed to start because the application configu...

Turn Off Updating of Ref Fields in Word 2007
I have a document that someone used Ref fields to link to bookmarked figures and hyperlinked them to the paragragh thus inserting a copy of the figure everywhere they inserted the field. I unlinked the extra graphics from the paragraph but not from the actual text e.g. "Figure VII 2-2." Now the document has updated all of the fields and reinserted the figures back in. If it was one or two no big deal but we are talking about probably 75-100 extra graphics being inserted into the document. I need to turn off the Auto Updating of Fields on Save or whenever the heck i...

Serious outbound mail error (ldap)
In the last 24 hours our Ex2k3sp1 server has stopped sending outbound mail to any external user. Every message gets bounced with The following message to user@domain was undeliverable. The reason for the problem: 5.1.1 - Bad destination email address 'ldap reject' As far as we know we havent changed anything in the last 24 hours (!) and there is no errors of any kind showing in the evnt log. What on earth is happening. This is pretty serious as we now have no outbound mail. I cant seem to find any further info on the problem anywhere? Any suggestions? Al Blake, Canberra, Austr...

Error Message #3
When I select a combobox pulldown to get the list of text, i get this error message: not enough system resources to display completely. When I select ok the menu on the combobox works fine. I hope someone might beable to help with this. Thanks so much ...

Macro to switch to another sheet, grab ref to any cell, paste ref in current sheet??
I am looking for a way (either macro or VBA) to perform one simple task: while in *any cell* of your choice in the current worksheet, switch to another specific sheet, choose *any* cell, grab a reference to that cell (not its value), switch back to the first worksheet and slap in the reference of the cell in the other sheet. This would be the automated equivalent of doing the following manually: 1) Select *any cell* (say *F11*) in the current sheet (Call it "*Destination*"). 2) Press "=" (no "ENTER") 3) Click the tab for the other sheet (call it "*Source*&...