Upgraded to Excel XP, Excel 97 Vlookup's corrupted
 

We would like to upgrade to Excel 2002 or(XP) however we have a huge amount
of excel 97 files that are loaded with Vlookup formulas similar to

=VLOOKUP('F:\Prices-Dec 2004-Master Program\Master Pricing -
Pacesetter.xls'!Joist_Package_2,'F:\Prices-Dec 2004-Master Program\Master
Pricing - Pacesetter.xls'!Prices_To_Change,MATCH('F:\Prices-Dec 2004-Master
Program\Master Pricing - Pacesetter.xls'!Apaloosa_A_IX_Series,'F:\Prices-Dec
2004-Master Program\Master Pricing - Pacesetter.xls'!Model_List,0)+2,FALSE)

When we load on Excel 2002 all our Vlookup results have no answers (I can't
remember the exact symbol) so basically all our 100's of workbooks are
useless.

Our IT guys are stumped and tell me they can find no answer
Has any one had this happen? Help to find a cure would be fantastic. We
would love to upgrade.

Keith, having fun with my new hobby

Hi
what exact return error do you get?. Also try if opening the referenced file
helps to get the values

--
Regards
Frank Kabel
Frankfurt, Germany

Keith at Sterling wrote:
We would like to upgrade to Excel 2002 or(XP) however we have a huge
amount of excel 97 files that are loaded with Vlookup formulas
similar to

=VLOOKUP('F:\Prices-Dec 2004-Master Program\Master Pricing -
Pacesetter.xls'!Joist_Package_2,'F:\Prices-Dec 2004-Master
Program\Master Pricing -
Pacesetter.xls'!Prices_To_Change,MATCH('F:\Prices-Dec 2004-Master
Program\Master Pricing -
Pacesetter.xls'!Apaloosa_A_IX_Series,'F:\Prices-Dec 2004-Master
Program\Master Pricing - Pacesetter.xls'!Model_List,0)+2,FALSE)

When we load on Excel 2002 all our Vlookup results have no answers (I
can't remember the exact symbol) so basically all our 100's of
workbooks are useless.

Our IT guys are stumped and tell me they can find no answer
Has any one had this happen? Help to find a cure would be fantastic.
We would love to upgrade.

Keith, having fun with my new hobby

I will get out IT people to load Excel XP on again to get the exact error
message. I did not try to open the source file and will do that this time.
I did try to open 5 or 6 different files that were linked to different
sources and none of them worked.

"Frank Kabel" wrote:

Hi
what exact return error do you get?. Also try if opening the referenced file
helps to get the values

--
Regards
Frank Kabel
Frankfurt, Germany

Keith at Sterling wrote:
We would like to upgrade to Excel 2002 or(XP) however we have a huge
amount of excel 97 files that are loaded with Vlookup formulas
similar to

=VLOOKUP('F:\Prices-Dec 2004-Master Program\Master Pricing -
Pacesetter.xls'!Joist_Package_2,'F:\Prices-Dec 2004-Master
Program\Master Pricing -
Pacesetter.xls'!Prices_To_Change,MATCH('F:\Prices-Dec 2004-Master
Program\Master Pricing -
Pacesetter.xls'!Apaloosa_A_IX_Series,'F:\Prices-Dec 2004-Master
Program\Master Pricing - Pacesetter.xls'!Model_List,0)+2,FALSE)

When we load on Excel 2002 all our Vlookup results have no answers (I
can't remember the exact symbol) so basically all our 100's of
workbooks are useless.

Our IT guys are stumped and tell me they can find no answer
Has any one had this happen? Help to find a cure would be fantastic.
We would love to upgrade.

Keith, having fun with my new hobby

When you open this workbook, you're prompted to update links (if you have that
setting). But if you answer no, you get a bunch of #ref! errors.

In earlier versions of excel, the old values were kept.

If this describes the problem...(from a previous post):

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?thre...GP11.phx .gbl

Maybe it'll work ok for you.



Keith at Sterling wrote:

We would like to upgrade to Excel 2002 or(XP) however we have a huge amount
of excel 97 files that are loaded with Vlookup formulas similar to

=VLOOKUP('F:\Prices-Dec 2004-Master Program\Master Pricing -
Pacesetter.xls'!Joist_Package_2,'F:\Prices-Dec 2004-Master Program\Master
Pricing - Pacesetter.xls'!Prices_To_Change,MATCH('F:\Prices-Dec 2004-Master
Program\Master Pricing - Pacesetter.xls'!Apaloosa_A_IX_Series,'F:\Prices-Dec
2004-Master Program\Master Pricing - Pacesetter.xls'!Model_List,0)+2,FALSE)

When we load on Excel 2002 all our Vlookup results have no answers (I can't
remember the exact symbol) so basically all our 100's of workbooks are
useless.

Our IT guys are stumped and tell me they can find no answer
Has any one had this happen? Help to find a cure would be fantastic. We
would love to upgrade.

Keith, having fun with my new hobby

--

Dave Peterson

The error message I get is #NA# everywhere. When I track down to the root
formula and activate the formula by clicking the checkmark at the top, most
of the calculations take place and quite a few numbers show up. However some
do not and they would be linked to the same workbook as the others. In
looking at the formula I see that the file path has changed, yet my IT people
tell me that they have not changed the location.

=VLOOKUP('\\calnas\sh-estim\Prices-July 2004-Master Program\Master Pricing -
Pacesetter.xls'!Fireplace_10,'\\calnas\sh-estim\Prices-July 2004-Master
Program\Master Pricing -
Pacesetter.xls'!Prices_To_Change,MATCH('\\calnas\s h-estim\Prices-July
2004-Master Program\Master Pricing -
Pacesetter.xls'!Pricing_3,'\\calnas\sh-estim\Prices-July 2004-Master
Program\Master Pricing - Pacesetter.xls'!Model_List,0)+2,FALSE)

The drive is still mapped the same yet XL is picking up the true location
spelling instead of the mapped name. This is most likely the issue and makes
sense when reading Dave Petersons comment of newer XL recalculating the
workbook. Any thoughts?

"Keith at Sterling" wrote:

I will get out IT people to load Excel XP on again to get the exact error
message. I did not try to open the source file and will do that this time.
I did try to open 5 or 6 different files that were linked to different
sources and none of them worked.

"Frank Kabel" wrote:

Hi
what exact return error do you get?. Also try if opening the referenced file
helps to get the values

--
Regards
Frank Kabel
Frankfurt, Germany

Keith at Sterling wrote:
We would like to upgrade to Excel 2002 or(XP) however we have a huge
amount of excel 97 files that are loaded with Vlookup formulas
similar to

=VLOOKUP('F:\Prices-Dec 2004-Master Program\Master Pricing -
Pacesetter.xls'!Joist_Package_2,'F:\Prices-Dec 2004-Master
Program\Master Pricing -
Pacesetter.xls'!Prices_To_Change,MATCH('F:\Prices-Dec 2004-Master
Program\Master Pricing -
Pacesetter.xls'!Apaloosa_A_IX_Series,'F:\Prices-Dec 2004-Master
Program\Master Pricing - Pacesetter.xls'!Model_List,0)+2,FALSE)

When we load on Excel 2002 all our Vlookup results have no answers (I
can't remember the exact symbol) so basically all our 100's of
workbooks are useless.

Our IT guys are stumped and tell me they can find no answer
Has any one had this happen? Help to find a cure would be fantastic.
We would love to upgrade.

Keith, having fun with my new hobby

When you open the workbook, do you answer yes to update links?

If you don't get the prompt, turn that prompt on via:
tools|Options|Edit Tab
check the "ask to update automatic links" checkbox.

If you answer no, try it with yes.

How about if you Edit|links|update values, does that help?

If that doesn't work, if you
edit|replace
what: = (equal sign)
with: = (equal sign)
replace all

What happens?

Keith at Sterling wrote:

The error message I get is #NA# everywhere. When I track down to the root
formula and activate the formula by clicking the checkmark at the top, most
of the calculations take place and quite a few numbers show up. However some
do not and they would be linked to the same workbook as the others. In
looking at the formula I see that the file path has changed, yet my IT people
tell me that they have not changed the location.

=VLOOKUP('\\calnas\sh-estim\Prices-July 2004-Master Program\Master Pricing -
Pacesetter.xls'!Fireplace_10,'\\calnas\sh-estim\Prices-July 2004-Master
Program\Master Pricing -
Pacesetter.xls'!Prices_To_Change,MATCH('\\calnas\s h-estim\Prices-July
2004-Master Program\Master Pricing -
Pacesetter.xls'!Pricing_3,'\\calnas\sh-estim\Prices-July 2004-Master
Program\Master Pricing - Pacesetter.xls'!Model_List,0)+2,FALSE)

The drive is still mapped the same yet XL is picking up the true location
spelling instead of the mapped name. This is most likely the issue and makes
sense when reading Dave Petersons comment of newer XL recalculating the
workbook. Any thoughts?

"Keith at Sterling" wrote:

I will get out IT people to load Excel XP on again to get the exact error
message. I did not try to open the source file and will do that this time.
I did try to open 5 or 6 different files that were linked to different
sources and none of them worked.

"Frank Kabel" wrote:

Hi
what exact return error do you get?. Also try if opening the referenced file
helps to get the values

--
Regards
Frank Kabel
Frankfurt, Germany

Keith at Sterling wrote:
We would like to upgrade to Excel 2002 or(XP) however we have a huge
amount of excel 97 files that are loaded with Vlookup formulas
similar to

=VLOOKUP('F:\Prices-Dec 2004-Master Program\Master Pricing -
Pacesetter.xls'!Joist_Package_2,'F:\Prices-Dec 2004-Master
Program\Master Pricing -
Pacesetter.xls'!Prices_To_Change,MATCH('F:\Prices-Dec 2004-Master
Program\Master Pricing -
Pacesetter.xls'!Apaloosa_A_IX_Series,'F:\Prices-Dec 2004-Master
Program\Master Pricing - Pacesetter.xls'!Model_List,0)+2,FALSE)

When we load on Excel 2002 all our Vlookup results have no answers (I
can't remember the exact symbol) so basically all our 100's of
workbooks are useless.

Our IT guys are stumped and tell me they can find no answer
Has any one had this happen? Help to find a cure would be fantastic.
We would love to upgrade.

Keith, having fun with my new hobby




--

Dave Peterson