#VALUE! error: vlookup works in Excel 2000 but not 2003

Hi,

My client has a spreadsheet which works fine in Excel 2000 but when opened 
with Excel 2003 it populates the pages with #VALUE! and the best I can tell 
is that the problem is with the following formulas.

Can anyone advise if they have seen this before.  I am unable to post their 
file but this is the code that I think is a bit suspect.

=IF(ISERROR(VLOOKUP(B14,'C:\Documents and Settings\User\My Documents\[Price 
List 040301.xls]Items'!$A:$M,11,FALSE))=TRUE,"",VLOOKUP(B14,'C:\Documents 
and Settings\User\My Documents\[Price List 
040301.xls]Items'!$A:$M,11,FALSE))


Many thanks,

Nick Ersdown 


0
anon1 (58)
11/24/2005 6:23:44 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
792 Views

Similar Articles

[PageSpeed] 5

Value errors in a lookup function occur either if there is a value error in 
the list itself or if you calculate with the lookup function. Does your 
client use the looked up value in a calculation then the lookup value is 
text (or the other value is text) if not another formula created the value 
either in the lookup table itself or as part of the same formula that holds 
the lookup. I have never had any problems with errors using the same raw 
data between different excel versions,
if somehow this only happens in 2003 I assume that somehow it was converted 
to text, also make sure your client has the latest service pack installed

note that the =TRUE is not necessary, if ISERROR is TRUE you don't have to 
test for it with =TRUE

-- 
Regards,

Peo Sjoblom

(No private emails please)


"Nick Ersdown" <anon@anon.com> wrote in message 
news:dm50fh$45t$1$8302bc10@news.demon.co.uk...
> Hi,
>
> My client has a spreadsheet which works fine in Excel 2000 but when opened 
> with Excel 2003 it populates the pages with #VALUE! and the best I can 
> tell is that the problem is with the following formulas.
>
> Can anyone advise if they have seen this before.  I am unable to post 
> their file but this is the code that I think is a bit suspect.
>
> =IF(ISERROR(VLOOKUP(B14,'C:\Documents and Settings\User\My 
> Documents\[Price List 
> 040301.xls]Items'!$A:$M,11,FALSE))=TRUE,"",VLOOKUP(B14,'C:\Documents and 
> Settings\User\My Documents\[Price List 040301.xls]Items'!$A:$M,11,FALSE))
>
>
> Many thanks,
>
> Nick Ersdown
> 

0
terre081 (3244)
11/24/2005 6:34:07 PM
OK, many thanks for your advice.  I will have a good look at the formulas 
and see if I can spot what you have suggested.

Regards,

Nick

"Peo Sjoblom" <terre08@mvps.org> wrote in message 
news:usYluWS8FHA.472@TK2MSFTNGP15.phx.gbl...
> Value errors in a lookup function occur either if there is a value error 
> in the list itself or if you calculate with the lookup function. Does your 
> client use the looked up value in a calculation then the lookup value is 
> text (or the other value is text) if not another formula created the value 
> either in the lookup table itself or as part of the same formula that 
> holds the lookup. I have never had any problems with errors using the same 
> raw data between different excel versions,
> if somehow this only happens in 2003 I assume that somehow it was 
> converted to text, also make sure your client has the latest service pack 
> installed
>
> note that the =TRUE is not necessary, if ISERROR is TRUE you don't have to 
> test for it with =TRUE
>
> -- 
> Regards,
>
> Peo Sjoblom
>
> (No private emails please)
>
>
> "Nick Ersdown" <anon@anon.com> wrote in message 
> news:dm50fh$45t$1$8302bc10@news.demon.co.uk...
>> Hi,
>>
>> My client has a spreadsheet which works fine in Excel 2000 but when 
>> opened with Excel 2003 it populates the pages with #VALUE! and the best I 
>> can tell is that the problem is with the following formulas.
>>
>> Can anyone advise if they have seen this before.  I am unable to post 
>> their file but this is the code that I think is a bit suspect.
>>
>> =IF(ISERROR(VLOOKUP(B14,'C:\Documents and Settings\User\My 
>> Documents\[Price List 
>> 040301.xls]Items'!$A:$M,11,FALSE))=TRUE,"",VLOOKUP(B14,'C:\Documents and 
>> Settings\User\My Documents\[Price List 040301.xls]Items'!$A:$M,11,FALSE))
>>
>>
>> Many thanks,
>>
>> Nick Ersdown
>>
> 


0
anon1 (58)
11/24/2005 8:35:41 PM
Is the path identical on both machines?

Do you both have Windows XP?

Pete

0
pashurst (2576)
11/24/2005 11:12:50 PM
Good point, I have never heard different excel versions creating value 
errors with the same workbook but I have seen posts where different windows 
versions do it

-- 
Regards,

Peo Sjoblom

(No private emails please)


"Pete" <pashurst@auditel.net> wrote in message 
news:1132873970.684367.27650@o13g2000cwo.googlegroups.com...
> Is the path identical on both machines?
>
> Do you both have Windows XP?
>
> Pete
> 

0
terre081 (3244)
11/24/2005 11:37:37 PM
xl2002+ likes to recalculate any workbooks that were created in previous
versions.  In earlier versions of excel, if you answer No to the update links
prompt, the existing values are kept.  In xl2002+, you get those errors.

Jim Rech posted a registry tweak:
http://groups.google.com/groups?threadm=uzkujhMHEHA.3284%40TK2MSFTNGP11.phx.gbl

Maybe it'll work for you.

Nick Ersdown wrote:
> 
> Hi,
> 
> My client has a spreadsheet which works fine in Excel 2000 but when opened
> with Excel 2003 it populates the pages with #VALUE! and the best I can tell
> is that the problem is with the following formulas.
> 
> Can anyone advise if they have seen this before.  I am unable to post their
> file but this is the code that I think is a bit suspect.
> 
> =IF(ISERROR(VLOOKUP(B14,'C:\Documents and Settings\User\My Documents\[Price
> List 040301.xls]Items'!$A:$M,11,FALSE))=TRUE,"",VLOOKUP(B14,'C:\Documents
> and Settings\User\My Documents\[Price List
> 040301.xls]Items'!$A:$M,11,FALSE))
> 
> Many thanks,
> 
> Nick Ersdown

-- 

Dave Peterson
0
petersod (12005)
11/25/2005 3:19:01 AM
Hi All,

Thanks for your advice.  I have had a good play around and the bulk of my 
problems are coming from the old trick for returning empty cells instead of 
a zero value - ,"",

Excel 2003 treats it as a non numeric value and the formula generates the 
error message.  I changed it to be a 0 instead of "" and the formulas are 
working okay now.

Many thanks,

Nick Ersdown


"Peo Sjoblom" <terre08@mvps.org> wrote in message 
news:%23A85UAV8FHA.1864@TK2MSFTNGP12.phx.gbl...
> Good point, I have never heard different excel versions creating value 
> errors with the same workbook but I have seen posts where different 
> windows versions do it
>
> -- 
> Regards,
>
> Peo Sjoblom
>
> (No private emails please)
>
>
> "Pete" <pashurst@auditel.net> wrote in message 
> news:1132873970.684367.27650@o13g2000cwo.googlegroups.com...
>> Is the path identical on both machines?
>>
>> Do you both have Windows XP?
>>
>> Pete
>>
> 


0
anon1 (58)
11/25/2005 12:23:01 PM
Reply:

Similar Artilces:

From Outlook 2000 to Outllook 2003
How do I migrate I personal folders file (.pst) from Outlook 2000 to Outlook 2003? Read the Help Files: http://office.microsoft.com/en-us/assistance/HA010771141033.aspx -- Russ Valentine [MVP-Outlook] "rolo" <rolo@discussions.microsoft.com> wrote in message news:706405A0-2971-409F-B213-67714B12713C@microsoft.com... > How do I migrate I personal folders file (.pst) from Outlook 2000 to > Outlook > 2003? Thanks Russ it helped. By the way how can I get to this useful help files? "Russ Valentine [MVP-Outlook]" wrote: > Read the Help Files: > htt...

Vlookup #65
Why is it that often this formula doesn't work? Do I need to clean up cells, or do they need to be in the same format? :confused -- Fran ----------------------------------------------------------------------- Frank's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=46 View this thread: http://www.excelforum.com/showthread.php?threadid=49784 It would help if you could advise the syntax of the formula you are trying to use and what you mean by 'it doesn't work', i.e. is it returning #N/A or is it returning the wrong result? In the meantime, have...

Unable to Save in Publisher 2000 to Flash Drive
I work in a school district and one of our teachers uses Flash Drives/Memory Keys (whatever you want to call them) for her students to save their projets on. She's starting a unit on Microsoft Publisher and had the students open a template off the flash drive and change it and resave in a folder on the flash drive but all the students are getting the following error: "There is no disk in the floppy drive or the drive door is not closed. Please make sure that the floppy drive is ready, then try again." -It is not a permissions issue - even the teacher is unable to save to...

Excel button problem
Hi All I have a macro that copies a worksheet in the active workbook and puts it into a new workbook - then formats it and deletes any buttons on the worksheet. On the first click on the button the macro works ok. On the second click, it fails because the all assigned macros on all buttons in the active workbook changed from "mba" to "book1!mba". Book 1 doesn't exists (wasn't opened, wasn't saved, doesn't have the macros). I've never experienced this problem before?? Can anyone help to solve this problem? FYI The macro to do this is c...

Excel 97 #9
Please can anyone help??? I have two columns in Excel 97. The first contains a list of statu values eg. pending, or granted or withdrawn. The second contains date eg.01/12/1997, 05/06/2003. I woudl like to know how to get all th granted apps before 31/12/2003. Can anyone help please -- Message posted from http://www.ExcelForum.com theres many ways, but an easiest way would be to do a sort. Highlight the 2 columns, click on data, then sort, then sort by status, then by date. this should group them all together. hope this helps...toe >-----Original Message----- >Please can anyo...

how to convert lookup values to the "display text"
I'm using an sql code (below) which uses a few lookup fields. Unfortunately in the datasheet view, I get the "bound values" instead of the "display values". How can I change the properties for the these lookup fields so I can see the "display values" from the datasheet view? SELECT [Funding],[Date],[Description],[Company],[Expense_Type],[Amount],[Status] FROM [Form_9_Status] UNION ALL SELECT [Funding],[Date],[Description],[Company],[Expense_Type],[Amount],[Status] FROM [TDY_Status] UNION ALL SELECT [Funding],[Date],[Description],[C...

vlookup #19
I have a workbook with 3 pages. One is a form, one is a job list, and the last one is a vendor list. On the form, I have two cells I fill with vendor # & job # information, which I want to fill in below in other cells of the form. I can get one of the vlookup formulas to work, the other one comes back with either the wrong job location or #N/A. My formula looks like this: =VLOOKUP(L1,'Job List'!$A$1:$AY$9999,2,FALSE). My job list and vendor lists are simply two columns each; identifying number in column A, and Vendor name/Job location in Column B. Help? U�ytkownik "HR&...

Outlook 0x800ccc0d error when Norton e-mail protect enabled: see hosts
This post is made to help others solve this issue, based on my experience. Symptom: - Outlook works perfectly well when Norton Anti-Virus e-mail protection is disabled - Outlook cannot retrieve incoming messages when NAV e-mail protection is enabled, message being: pop3 server not found, error 0x800ccc0d This symptom, and possible solutions, are exactly the object of Symantec support note: http://service1.symantec.com/SUPPORT/nav.nsf/docid/2000020716064206 Please read this note first ! The object of this post is to add another possible solution to this problem. NAV email protection sets up...

Looking for Excel Help
I'm a very novice Excel user and am looking for a little help with creating a formula for a spreadsheet I'm creating for my personal use. I would appreciate some assistance if possible. Thanks in advance. Dan --- Message posted from http://www.ExcelForum.com/ Hi Dan! Post a sample of what you want to do. Your question is just a tad open ended <g> -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "DanB4105" <DanB4105.ywtpa@excelfor...

New to excel
Hi All, I'm new to Excel ( and to this forum :) ) and so I hope somebody may b able to help me. I've got 2 questions.... QUESTION 1 I've got a spreadsheet which takes data from one worksheet and uses i to calculate data in a second worksheet using the following code formula: =IF('4th November 2005'!B19="","nothing here dude",IF(B19<'4th Novembe 2005'!B19,"UP",IF(B19='4th November 2005'!B19,"Same",IF(B19>'4t November 2005'!B19,"DOWN")))) The problem is, when I create a new worksheet I have...

will CRM load on a 2003 server?
will CRM load on a 2003 server? Microsoft CRM v1.2 supports Windows 2000/2003 Server. Frank Lee Workopia, Inc. >> Other Microsoft CRM Online Forum Resources: http://www.workopia.com/Links.htm >-----Original Message----- >will CRM load on a 2003 server? >. > No problem. We just completed a 1.2 installation on a 2003 server, without any problems. Brian Demoe "Troy Hicks" <tlhicks@nc.rr.com> wrote in message news:03dd01c3dcb2$93653a00$a501280a@phx.gbl... > will CRM load on a 2003 server? CRM 1.2 will also load on Small business server 2003 as wel...

Cannot open Outlook, keep getting Send error report of Microsoft.
We are having problems with Outlook 2003. Everytime we try to open, we get this: Send error report to Microsoft. Any ideas why? All windows and office update have been completed. ...

setup Windows Mail as Word 2003 default emailer
All I can do is setup Outlook. I do not use Outlook. I would like to email Word docs using MS Windows Mail (new version of Express) In the Windows Start area, type Regedit into the search bar and then start the Registry Editor and go to HKEY_CURRENT_USER>Software>Clients>Mail Right Click on the (Default) item and then on Modify and in the Value data: field enter Windows Mail so that after you click OK, you have (Default) REG_SZ WIndows Mail -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a pa...

Filters not working in Exchange 2003
I have been trying to turn on the Recipient, Connection, and Sender filters. I have gone to the Default SMTP Virtual Server and turned it on there without getting an error but when I go to the Properties and add senders to block and the hit Apply, it tells me that I must manually turn the filtering on in the SMTP VS. I have stopped and started the Default SMTP VS but still no luck. Any ideas? Hi Wayne That is a standard dialog box, it does not check to see if it is already enabled, have you tested the sender filtering? -- Mark Fugatt Microsoft Limited This posting is provided &quo...

Document Viewer installer error
I recently ran CHKDSK /F as this was prompted when I attempted to run defrag etc. Since the chkdsk was run, when I turn my PC on, I receive the following message: "document viewier - installed encountered an unexpected error - error code 2908". I click run, the same error just repeats and loops. The only was I can remove it is to end task via task manager. Any ideas how I can correct this error? many thanks ...

Redirect Exchange 2000 IS backup to different Exchange 2003 server
I recently added an Exchange 2003 server to the same org as a 2000 server. I have dbs from the 2000 server that I need to restore to retrieve email from a user whose mailbox was moved to 2003. So I need to restore the db for that mailbox from BEFORE it was moved because when you move mailboxes you lose any deleted items that were being saved by retention policy. Is this possible? I'm using Veritas Backup Exec 10 but nothing in their support KB seems to follow this exact scenario. If it helps, the old Exch 2000 server is currently empty of users and is ready to be uninstalled. W...

Outlook 2003 Drag and Drop Emails
I have an issue where there is a SBS 2003 server (newly installed) & when I drag emails to the file system (explorer window) in order to create file records of the emails it generates an error. Dialog Box Name: Error Copying File or Folder Error Msg: Not enough storage is available to process this command. I can't find an error logged anywhere, either on the server event logs or on the local machine event logs... I have searched the MS KB & Office online, but no joy yet... If anyone can help that would be great!!! R ...

CSV Files and VLOOKUP error
Does anyone know why VLOOKUP and Compare formulas don't work o information originating from a CSV file? I've tried copying an pasting values only (to leave behind any formatting), but it doesn' help. Through countless tests, I've narrowed it down to the CSV file bein the only possible cause -- Message posted from http://www.ExcelForum.com Hi ajpowers, Just a guess but the imported data may have leading or trailing spaces or are numbers stored as text. You could use the formula =A1=D1 to see if you get a true or false, where A1 is the lookup value and D1 ia the CVS valu...

Uninstall of mappoint has caused errors with excel
Hi, I am running Office 2003 on the terminal server (windows 2003) and had a copy of mappoint as well. This is a mapping program. We ininstalled mappoint which has caused an error message with Excel and other office products. The error says "Cd:\documents and settings\administrator.ocrdc1\application data\microsoft\addins c:\Program files\common files\microsoft shared\geography\mpoai9.dll is not a valid add-in." I then click OK and excel opens up and everything is fine. The problem is that we are using other programs as well such as Quickbooks that export to excel and t...

Excel corrupts when asking to update vlookups
We are experiencing weird behavior with some Office 2K3 Excel spreadsheets that contain lots of calculations, but no macros. On some pc’s Excel acts normally, on others you get the error. I have a couple of screen shots available. Any help is appreciated. If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Gu...

How to delete a set of rows depending on Value
I have two worksheets Worksheet A 27 Columns 1600 Rows. Worksheet B 1 Column 200 Rows I need Worksheet A to look at each cell in Worksheet B, if a cell exists in Worksheet A, then I need the row deleted... Basically I have a list of Grand list of items to do, then a list of items already completed. I need to now remove all entries in the grand list that have been completed. Is this feasible or should I look at using some Unix script. It sounds like you could use VLOOKUP to find out if the value in B exists on A: http://www.officearticles.com/excel/vlookup_formulas_in_microsoft_excel....

'top' is not a valid value for the 'vertical-align' property.
Well, it is, actually, and the resulting .chm file works properly in HTML Help. But it's a bit tedious getting that wrong useless error message from VS2002 on every build. (Well, not every build, it only does it sometimes actually.) Any idea how I can persuade it to stop telling me this? -- Tim Ward Brett Ward Limited - www.brettward.co.uk ...

Unknown error from CFtpConnection::GetFile()...
Hi all, I'm having intermittent problems with CFtpConnection::GetFile()... 99% of the time GetFile() succeeds but occasionally it fails and returns FALSE with a GetLastError of 0x2EFF (12031) which I can't find documented/defined anywhere... Anyone know what this error means...? Where it is defined...? A bit of searching on on the web seems to suggest; "The connection with the server has been reset." Can anyone confirm this...? Many thanks, Andy. Andrew Kilgore wrote: > Hi all, > > I'm having intermittent problems with CFtpConnection::GetFile()... > 99%...

How do I Remove a Split from my Comments in Excel 2003? #2
I have set my current workbook to split/freeze the first column and first 2 rows. Now, when I add a comment to the second row (in any column) my comments are cut off if I should scroll down. I don't ever remember the behavior before. And I don't know what I've done to enable it but it's really annoying. How do turn this off ? ...

Error 550 Relaying denied
I get this nearly every time I try to send information using MS Outlook email. Any suggestions? You probably need to turn on authentication to your outgoing server. You can do so on the "Outgoing Server" tab for your mail account. -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:9cf101c3eacf$a3250cb0$a401280a@phx.gbl, Dick Brenneke wrote: > I get this nearly every time I try to send information > using MS Outlook email. Any suggestions? Th...