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

[Nick Ersdown]

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

 

[Peo Sjoblom]

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]

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

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)

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

 

[Pete]

Is the path identical on both machines?

Do you both have Windows XP?

Pete

 

[Peo Sjoblom]

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)

 

[Dave Peterson]

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/[email protected]

Maybe it'll work for you.

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

 

[Nick Ersdown]

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