vlookup functions are replaced with result on save

%3CLINGO-SUB%20id%3D%22lingo-sub-2646481%22%20slang%3D%22en-US%22%3Evlookup%20functions%20are%20replaced%20with%20result%20on%20save%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2646481%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20file%20from%20a%20client%20saved%20in%20.xls%20format%20(I%20don't%20know%20which%20version%20of%20Excel)%20that%20has%20many%20vlookup%20functions%20in%20the%20file.%26nbsp%3B%20Once%20I%20enter%20data%20in%20the%20file%20and%20save%2C%20all%20of%20the%20cells%20containing%20a%20vlookup%20function%20are%20replaced%20with%20the%20results%20of%20the%20formula.%26nbsp%3B%20My%20searching%20so%20far%20has%20only%20lead%20to%20instruction%20on%20how%20to%20do%20this%20manually%20which%20I%20already%20know%20how%20to%20do%20(copy%2C%20paste-special%2C%20values).%26nbsp%3B%20I'm%20trying%20to%20stop%20it%20from%20happening%20automatically.%26nbsp%3B%20Is%20there%20a%20setting%20I%20don't%20know%20about%20that%20needs%20to%20be%20changed%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can't%20attach%20the%20file%20with%20the%20vlookup%20function%20shown%20because%20they%20are%20removed%20upon%20saving%2C%20but%20here%20are%20two%20examples%20of%20where%20they%20should%20be%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BPI-Roadway-Drainage%20sheet%2C%20B7%20%3D%20'%3DVLOOKUP(C7%2C'Item%20List'!%24A%242%3A%24B%241197%2C2%2CFALSE)'%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BPI-Roadway-Drainage%20sheet%2C%20E7%20%3D%20'%3DVLOOKUP(B7%2C'Item%20List'!%24B%242%3A%24C%241197%2C2%2CFALSE)'%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20file%20from%20the%20client%20does%20have%20some%20macros%20in%20it%20but%20the%20problem%20continues%20with%20the%20active%20content%20disabled.%26nbsp%3B%20I%20still%20looked%20through%20the%20macros%20to%20check%20and%20found%20nothing%20that%20would%20explain%20the%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2646481%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2646506%22%20slang%3D%22en-US%22%3ERe%3A%20vlookup%20functions%20are%20replaced%20with%20result%20on%20save%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2646506%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1127978%22%20target%3D%22_blank%22%3E%40David13752230%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20there%20be%20an%20active%20add-in%20that%20causes%20this%3F%20The%20workbook%20itself%20does%20not%20remove%20formulas%20upon%20save%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0664.png%22%20style%3D%22width%3A%20562px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F303172i98C344AB3772D0C8%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S0664.png%22%20alt%3D%22S0664.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIf%20you%20open%20the%20attached%20version%2C%20are%20the%20formulas%20still%20present%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E(Remark%3A%20the%20workbook%20contains%20a%20lot%20of%20non-functional%20code)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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.

 

 

 

2 Replies

@David13752230 

Could there be an active add-in that causes this? The workbook itself does not remove formulas upon save:

S0664.png

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.