VLOOKUP in Excel in driving me insane 1

[PenelopeC]

Hi,
I have one workbook with a master list of phone numbers and the charges incurred last month. In the second workbook I have a subset of phone numbers with NO charges associated, however their cost center is in another column. I'm looking for last month's charges to be added to this subset.

I use VLOOKUP. I have all the phone numbers as straight numbers (no dashes or parentheses around the area code), both worksheets have the phone as the first column. I fill in the rest of the formula no problem. When I hit the enter button "N/A" comes up. I thought I was doing the formula wrong BUT then I discovered that if I double clicked the cell that I'm looking up (in other words, in the subset worksheet where the formula is located) the "N/A" mysteriously changes to the amount in the column I'm returning if the lookup field is found ONCE I CLICK ON THE NEXT CELL.

In a nutshell, I've got hundreds of cells to double-click to make my formula work! What is up with this??? Does anyone know a work around to (apparently) refresh the data the formula is looking for?

I'm using Excel 97 (SR-1) on a Win2K box. Any help is much appreciated!! TIA

PenelopeC
~~~>-/O~~~~~swimming right along

 

[SkipVought]

Penelope,

Do you have Calculate set to Automatic?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[substitute]

If Skip's suggestion isn't the answer, as you are using Excel 97 you might have triggered the Excel 97 "doesn't recalculate newly entered formulas" bug. This is a problem in early versions of Excel 97 where the recalculation engine fails to realise a newly entered formula needs to be calculated. By double clicking (editing) a source cell you force that cell to be recalculated and so force a recalculation of the lookup formula.

The best solution to is to pick up the latest Office 97 service pack from the Microsoft Office web site if it is still available. If it isn't, upgrade to 2002.

Otherwise you could try saving your data as a CSV file and re-opening it to flush out the gremlins.

 

[PenelopeC]

Skip - I checked that one...and yup I had it on.

substitute - Thanks! I did help/about and thought since I had SR-1 I was good to go...luckily MS still had the service pack on their site and after I installed it everything works perfect.

I heard that upgrading to Office 2000 will render Access97 confused at the least and useless at the most. I have to work with legacy Access97 packages which is why I haven't upgraded to O2K (though we certainly have the software available) If the above rumor is not true, can you think of any reason why I shouldn't upgrade if I can use 97 and 2K on the same box?

Thanks so much for your help! I'm keeping the service pack archived in case anyone ever needs it but can't find it! It's the least I can do. :-{}

PenelopeC
~~~>-/O~~~~~swimming right along

 

[substitute]

Glad that SP helped - it happened to me about 3 years ago and I simply couldn't believe what I was seeing until I found a Microsoft support article describing it.

If you move to Office 2000 and Access 2000 you can still work with an Access 97 database, but when you first open it you get two choices:

* Leave it in 97 format in which case you can't change anything (except presumably the data)

* Convert it to 2K format in which case '97 users can't use the new database at all (the '97 database isn't touched and they can continue to use that)

You could do a custom install of Office 2000 and leave out Access. It should leave you with Access 97. I would try it on someone else's PC first!

Good luck

 

[PWD]

Just an aside on the Access upgrade thing. There's a known problem where you try to run Access 97 and it tells you there isn't a license on the PC. Microsoft have released what to me appears to be the craziest workaround ever where you have to delete a font 'hatten.ttf' and do other stuff to fix it.

Des.