Forum Discussion
vlookup functions are replaced with result on save
I have a file from a client saved in .xls format (I don't know which version of Excel) that has many vlookup functions in the file. Once I enter data in the file and save, all of the cells containing a vlookup function are replaced with the results of the formula. My searching so far has only lead to instruction on how to do this manually which I already know how to do (copy, paste-special, values). I'm trying to stop it from happening automatically. Is there a setting I don't know about that needs to be changed?
I can't attach the file with the vlookup function shown because they are removed upon saving, but here are two examples of where they should be:
PI-Roadway-Drainage sheet, B7 = '=VLOOKUP(C7,'Item List'!$A$2:$B$1197,2,FALSE)'
PI-Roadway-Drainage sheet, E7 = '=VLOOKUP(B7,'Item List'!$B$2:$C$1197,2,FALSE)'
The file from the client does have some macros in it but the problem continues with the active content disabled. I still looked through the macros to check and found nothing that would explain the problem.
Could there be an active add-in that causes this? The workbook itself does not remove formulas upon save:
If you open the attached version, are the formulas still present?
(Remark: the workbook contains a lot of non-functional code)
- Opening the attached version, the formulas are still present, and are still present after saving it again. This lead to another test. I downloaded the attached version to my computer, uploading it onto the server, and the behavior returns. Same is true in reverse for my original file. If I copy the file onto my machine's hard drive, the formulas don't disappear anymore. I've never seen this happen before and not sure if an add-in is at fault or something else. But at least I now have more information on when it happens and when it doesn't.
I ran across a similar problem today (1/15/2024) where my Vlookup formulas were being replaced with the function results on saving the workbook. A long story short I first needed to upgrade the Excel version from latest version of Excel (From XLS to XLSX) . Secondly the auto save function would enable, giving a message to delete emended OLX, (Which were saved in hidden sheets). After updating to the latest version of XL, deleting the hidden sheets and being able to enable the autosave function the problem disappeared.
Hope this helps.
- I forgot to add, this problem was only experienced when using Sharepoint (and opening the file in App), however when downloaded the file to the local drive there was no problem with the Vlookup function being replaced by its result.
