Home

Escel - how to reverse "Replace formulas with their calculated values"

%3CLINGO-SUB%20id%3D%22lingo-sub-556432%22%20slang%3D%22en-US%22%3EEscel%20-%20how%20to%20reverse%20%22Replace%20formulas%20with%20their%20calculated%20values%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-556432%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20inherited%20a%20large%20customised%20workbook%20and%20need%20to%20make%20some%20changes%20to%20repair%20an%20error%20that%20I%20have%20found.%3C%2FP%3E%3CP%3EIn%20one%20of%20the%20sheets%20there%20is%20a%20cell%20with%20a%20value.%20This%20value%20changes%20based%20on%20a%20selection%20in%20another%20sheet%2C%20but%20the%20cell%20in%20question%20contains%20no%20formula.%20It%20just%20has%20a%20value.%20There%20is%20also%20no%20VBA%20code%20present%20to%20push%20a%20value%20to%20the%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20only%20thing%20that%20I%20can%20think%20of%20is%20that%20the%20cell%20has%20at%20some%20time%20had%20the%20formula%20replaced%20with%20its%20calculated%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20reverse%20this%20to%20recover%20the%20original%20formula%3F%20The%20formula%20must%20still%20exist%20in%20the%20workbook%20otherwise%20the%20cell%20would%20not%20update%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-556432%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-556689%22%20slang%3D%22en-US%22%3ERe%3A%20Escel%20-%20how%20to%20reverse%20%22Replace%20formulas%20with%20their%20calculated%20values%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-556689%22%20slang%3D%22en-US%22%3EI%20believe%20you%20have%20to%20recreate%20the%20formula%20yourself.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-561836%22%20slang%3D%22en-US%22%3ERe%3A%20Escel%20-%20how%20to%20reverse%20%22Replace%20formulas%20with%20their%20calculated%20values%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-561836%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20the%20info%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%2C%26nbsp%3BI%20was%20afraid%20of%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20the%20problem%20is%20that%20there%20are%20approximately%2015%20worksheets%20and%20I%20have%20no%20idea%20what%20data%20the%20original%20formula%20is%20referencing%20without%20seeing%20the%20formula.%20It%20includes%20complicated%20electrical%20calculations%20across%20several%20worksheets%20to%20produce%20a%20result.%20The%20result%20of%20the%20formula%20is%20now%20creating%20an%20error%20under%20some%20(new)%20scenarios%20and%20I%20need%20to%20review%20the%20formula%20to%20thereby%20correct%20the%20original%20data-sets%20that%20the%20formula%20is%20working%20through.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20you%20can%20imagine%20it%20is%20not%20possible%20to%20reverse-engineer%20the%20formula.%20Tracing%20precedents%20offers%20nil%20results%2C%20and%20tracing%20dependants%20of%20course%20shows%20everything%20that%20subsequently%20uses%20the%20result%20for%20further%20calculations.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20recreating%20the%20formula%20myself%20is%20not%20in%20this%20case%20remotely%20possible.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20there%20was%20a%20formula%20that%20has%20been%20replaced%20with%20the%20result%2C%20that%20formula%20%3CU%3E%3CSTRONG%3Emust%20still%20exist%3C%2FSTRONG%3E%3C%2FU%3E%20somewhere%20in%20the%20workbook%2C%20or%20the%20result%20would%20not%20calculate.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPerhaps%20someone%20out%20there%20knows%20a%20VB%20script%20that%20could%20help%20extract%20the%20replaced%20formula%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENick.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
nwrathall
New Contributor

Hi,

 

I have inherited a large customised workbook and need to make some changes to repair an error that I have found.

In one of the sheets there is a cell with a value. This value changes based on a selection in another sheet, but the cell in question contains no formula. It just has a value. There is also no VBA code present to push a value to the cell.

 

The only thing that I can think of is that the cell has at some time had the formula replaced with its calculated value.

 

Is there a way to reverse this to recover the original formula? The formula must still exist in the workbook otherwise the cell would not update?

 

Thanks in advance.

2 Replies
I believe you have to recreate the formula yourself.

Thanks for the info @Twifoo, I was afraid of that.

 

However, the problem is that there are approximately 15 worksheets and I have no idea what data the original formula is referencing without seeing the formula. It includes complicated electrical calculations across several worksheets to produce a result. The result of the formula is now creating an error under some (new) scenarios and I need to review the formula to thereby correct the original data-sets that the formula is working through.

 

As you can imagine it is not possible to reverse-engineer the formula. Tracing precedents offers nil results, and tracing dependants of course shows everything that subsequently uses the result for further calculations.

 

Just recreating the formula myself is not in this case remotely possible.

 

If there was a formula that has been replaced with the result, that formula must still exist somewhere in the workbook, or the result would not calculate.

 

Perhaps someone out there knows a VB script that could help extract the replaced formula?

 

Nick.

 

 

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies