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
0
keith363 (3)
12/15/2004 5:11:05 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
335 Views

Similar Articles

[PageSpeed] 16

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 


0
frank.kabel (11126)
12/15/2004 7:49:26 AM
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 
> 
> 
> 
0
keith363 (3)
12/16/2004 3:07:05 AM
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?threadm=uzkujhMHEHA.3284%40TK2MSFTNGP11.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
0
ec357201 (5290)
12/16/2004 3:29:05 AM
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\sh-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 
> > 
> > 
> > 
0
keith363 (3)
12/21/2004 3:49:01 AM
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\sh-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
0
ec357201 (5290)
12/21/2004 3:45:33 PM
Reply:

Similar Artilces:

number rounding in MS Excel
Hi, This may have been answered many times. In MS EXCEL is there a way that it calculates all numbers to 2 decimal digit accuracy instead of the 15 digit default? Thanks in advance for the answer. Hi You can set your decimal places in your cells as 2 and then check Precision As Displayed on the Tools / Options / Calculation page. Be careful though, it means what it says!! -- Andy. "mahusain" <abidh@bdnet.net> wrote in message news:ab59c6f6.0404200315.5196e8aa@posting.google.com... > Hi, > > This may have been answered many times. > > In MS EXCEL is ther...

Opening publisher 97 with a later version
I have publisher 97 on my windows xp and it works fine. However, I had someone refine some work I had done and apparently they used a later version of Publisher because when I loaded their CD, I got the message "Publisher cannot load files from a different version" What can I do--does this mean I have to buy a later version to match the version he used, or do I have to buy one of those programs that can open other programs. If I do the later, will I be able to use my 97 version to make changes once I get the files open on the later version Thanks rjda Refer to http://www.mvps....

Unable to open Outlook Express after upgrade to Win. XP
I upgraded from windows98 to windows XP Pro. Now when I try to use OE I get two error messages. "Outlook Express could not be started. The application was unable to open the OE message store. Your compter may be out of memory or your disk is full.....0x800c012e,3" "OE could not be started because MSOE.DLL could not be initialized. OE maynot be installd correctly" I found a similiar problem fix for window2000 which said to delete OE, rename the old folder, then edit the registry and reinstall. I did the above but was not allowed to rename the old folder and th...

Vlookup returns "0"
I am using the following formula =VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE and it is returning a zero if there is no data found in that cell. If there is no data found I would like it to display nothing. How can I do this? Carolyn, There are a couple ways so do this. One is to test it, which makes for doing the VLOOKUP function twice. 1) =if(VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE)=0, "" , VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE)) This gets a little messy. Better yet, and simpler is to hide the original cell (column, whatever), an...

SpreadSheetML (XML for Excel)
Hello All, A client of mine has Office X for Mac. I am wondering if this edition of Excel supports SpreadSheetML. I have heard that it also support the new Open XML formats (same as Office 11). Does anyone know if either of these statements is accurate? Thanks in advance, -KJ Hi KJ: I believe that both statements are wrong. The XML converter for Office Next on the Mac has not yet been developed. It's not likely to come until after the PC Office team ships their product to retail -- until they stop CHANGING the thing :-) My guess is that the converter won't appear until sometime...

Public Folder link to Excel File
Hi - I have a user that claims that he had a shortcut to a spreadsheet "in his public folders" at his old job. Obviously, he's an end user that may or may not know what he's talking about.... I'm trying to figure out the best way to accomodate him. He basically wants an Excel file stored on a shared folder to be available to all user as either a shortcut, or a link in the public folders (can't post to a folder as he will be updating the sheet regularly). When a user clicks on the shortcut/link, it would just launch Excel and display the file. Anybody have any ideas...

Excel Opens Without Displaying Workbook
I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Displaying Workbook (http://support.microsoft.com/default.aspx?scid=kb;en-us;158996&Product=xlw97), but neither of the resolutions fixed the problem. Any suggestions?? Are you using Excel 97? -John Baughman Fort Collins, CO >-----Original Message----- >I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Di...

Filters in Excel 2003
I am using Filters in Excel 2003. Every cell in sheet B is linked to every cell in sheet A. That way I can alter data in B without corrupting the original data in A. I then highlight the cells in B that I want to assign a filter to and select Advanced Filter. I have no criteria so I do not set that. I click OK. I then select Filter again and this time select Auto Filter. Drop down arrows appear at the top of my columns. Great, no problems so far, I then select from the drop down list the criteria that I want to filter. Again no problems, my list filters correctly. But once I have do...

Corrupt "Deleted Items" folder
I am unable to empty the "Deleted Items" folder. The error that comes up tells me that the "Outlook.pst" file has errors in it and to use the "Repair Inbox Tool". I've tried using the repair function under the "help" menu...to no avail. I have also tried opening the "Outlook.pst" file in MS Word, but the file is 129 megabytes! It crashes MS Word when I try to open it. Any ideas? Look for scanpst.exe on your local drive and run it against your outlook.pst file >-----Original Message----- >I am unable to empty the "Del...

Outlook 2003 with XP SP2
Hey guys, IS there an issue with outlook 2003 running on a XP machine with SP2. I am having connectivity problems. When I shut off the windows firewall, the outlook client connects. some help please. thanks, st I suggest that you add outlook2003 to the Windows Firewall exception list. Regards, -jack "stonefly" <shitij_T@hotmail.com> д����Ϣ����:OAoct1k5EHA.4028@TK2MSFTNGP15.phx.gbl... > Hey guys, > IS there an issue with outlook 2003 running on a XP machine with SP2. I > am having connectivity problems. When I shut off the windows firewall, the > outlo...

View original Excel after saving over it
In error, i hit save instead of save as. I'd like to view the original documents contents. is this at all possible. i use windows xp. excel 2003 Not very likely. Try a Google search action if you haven't already, but I think it's impossible. This underscores the importance of backups. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "houb" wrote: > In error, i hit save instead of save as. I'd like to view the original > documents contents. is this at all possible. i use windows xp. excel 2003 In article <C2A3F0E7-24E0-43A5-809A-ECA719...

printing imported gif files in Excel
When printing gif file with transparent background on the grey(25%) background of Excel non-transparent part of the picture has white border around it. Is there any way to prevent it? Thank you in advance... Tim ...

ExCel programming
I am working on a spreadsheet for work where it will calculate commissions to be earned based on my sales. There is a table that I have to use to determine what the percentage would be, how would I write an equation for that. For example, the equation would have to say: if say D3 was = or < than %, use cell# whatever and then have a sum in there. The table is on another sheet in the document so that would have to be there too. Basically, the way that my commissions are calculated is that we have to take what I sold in any given month and find out what percentage of my yearly plan i...

Some Excel formatting functions taking a long time to work #2
Excel 2000 SP3 When I use some formatting functions for the first time in a session, such as bold, increasing font size etc, it takes up to 30 seconds to work. Meantime Excel is locked up until it completes that formatting call. I suspect faulty DLL? Has anyone experienced this? How to fix (other than a complete re- install) ? Any advice appreciated. Piri On Nov 5, 8:39=A0am, Piri <wiremu.pare...@hotmail.com> wrote: > Excel 2000 SP3 > When I use some formatting functions for the first time in a > session, such as bold, increasing font size etc, it takes =A0up to 30 > secon...

setpassword and xp styles?
Hello I have added manifest file and enabled xp styles in my application. Now, i have EDIT1 mapped to m_edt1 that have a password style. When the application runs, the password character shows as a nice graphical black dot. Now if i call: m_edt1.SetPasswordChar(0) in order to remove the password char. Then if I call setpasswordchar('*') the black dot char never returns... Any ideas how to swap between non password field to password field (with that nice black dot character) ? -- Elias "lallous" <lallous@lgwm.org> wrote in message news:OoraL8o9FHA.3592@TK2M...

How to save Japanese characters in CSV format of Excel ?
Hi, I would like to know, how to save the Doubly-Byte characters.For example, Japanese in CSV of Excel.Here are the steps, I tried. 1. Open Excel 2. Copy pasted the Japanese charactesrs in a cell.It is displaying the characters correctly. 3. Save as CSV(Comma seperated file) It is saving as ??????. Thanks in advance for the info. ...

Saving e-mail & attachments when upgrading
I'm upgrading from NT4 to XP Home and the only data I need to keep are the emails & attachments. I have to format the hard drive to eliminate the partitions. This machine is on a network and I can save to the other machune. Look here for all the information you need on backup and restoration of Outlook data: http://www.slipstick.com/config/backup.htm -- Jocelyn Fiorello MVP - Outlook *** Replies sent to my e-mail address will probably not be answered -- please reply only to the newsgroup to preserve the message thread. *** "Mike" <mike@hosemart.com.au> wrote ...

Adding a Macro to a VLookup Function
In Excel 2000 -- I would like to create a summary spreadsheet (sheet2). For each time that "Name" appears in sheet 1, row *, take information from the same row, but columns 2 and 6 and bring it over to sheet 2 in the designated area. I know that you need to do a "VLookup" function and I got that to work. I know that you also need a "Loop" statement, so that it will continue to perform the Vlookup and bring over the information for each time that the name appears. I don't know how to write the formula so that the VLookup and the Loop are combined. Examp...

Macro
I need a macro that help me to transfer name and address information from an specific table in excel to a template in words on specific areas and then print the word document. The reason for this is that i need to create diferents letters to be sent to the customers from the excel table. Example of the table is: soc seg, customer name, child name, customer code, add 1 , add2, city, estate, zip code. all this information will be paste on word letter template on specific areas or fields. Any suggestion!!! -- nicoro Hi IMHO the best approach would be to set up a mail merge documen...

Outlook 2000 running on XP
After setting up a mail account and indicating that I want all passwords remembered I constantly get promoted to enter a password when I send and/or receive mail. I have clicked remember password in all dialoges during the setup process and via the Properties dialog following the creation of the account. Any thoughts or suggestions would be greatfully accepted Thanks "Ross Andrews" <anonymous@discussions.microsoft.com> wrote in message news:1fb4801c45886$24eab500$a301280a@phx.gbl... > After setting up a mail account and indicating that I > want all passwords reme...

Excel fun patch
Excel fun patch, have you see it? http://www.conus.info ...

Excel 2003
Sometimes when I receive excel attachments in my email (outlook 2003) they have a row height of 409.5 when I open them. I have to highlight the whole sheet and change the row height before I am able to view the data. This does not happen all the time and not from any particular person. I can forward the email to another machine with excel 2003 and they open it with no problem. I have downloaded all the patches and updates that I can find and still no help. This is just an annoying quirk that is driving me nuts. Does anyone have any ideas? TIA How about a couple of silly guesses that might t...

Need macro help to close excel
I have created a button in Access2000 that opens an Excel Spreadsheet. What I need now is assit in closing excel upon completion. I can get an excel macro to save my file and close the worksheet, but it is not closing excel entirely. I'm on project with this employer and could use a response today to fix this before I leave. Thanks much to any and all. My macro is as follows: Sub SaveClose() ' ' SaveClose Macro ' Macro recorded 9/27/2004 by cdjohnso ' ' Keyboard Shortcut: Ctrl+Shift+C ' ChDir "I:\SchoolsSurvey\Graphs_Reports" ActiveWorkb...

Excel Text Function (Right, Left)
My sheet has columns like this A 1. 63mmx 4kg 2. 75mmx 4kg SWR How can i detect that if a1:a2 is right,3 "swr", that it should be returned 3 otherwise 6. How can i add =right function with conditional formula. Help me Rao Ratan singh Hi =IF(RIGHT(A1,"SWR"),3,6) fill down your column (move cursor over bottom right hand corner of the cell when you see a +, double click) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Rao Ratan Singh" <RaoRatanSingh@discussions.microsoft.com> wrote in message news:D...

copy chart from Excel to Powerpoint
I am trying to copy a chart from Excel to Powerpoint, but the button is grayed out and it won't let me copy it. You can convert the chart to a GIF (a picture) with my Excel Objects converter. http://www.geocities.com/excelmarksway "Hoffperson" wrote: > I am trying to copy a chart from Excel to Powerpoint, but the button is > grayed out and it won't let me copy it. ...