Track formula change?

[Excelgovgeek]

I am racking my brain trying to figure out how to highlight the cell if it's formula has changed.

For example, the formula in the cell is: =IFERROR(SUM(K5,L5)*VLOOKUP($G5,OH,2,FALSE),0)

if a user changes the cell's formula to be something like: =IFERROR((K5)*VLOOKUP($G5,OH,2,FALSE),0) ,I'd like to be able to highlight the cell's color (essentially tracking if the cell has changed).

Not sure if this is possible but it seems like it should be able to be done. This is a workbook that is going out to a large user base so I would rather not use VBA as some of their networks may block macros and .xlsm docs.

Thanks in advance for any and all suggestions!

 

[louisH]

Hi,

If a lot of users are going to use the same workbook, you might transform it to a shared workbook.
Shared workbooks allow multiple users to work on the same file at once, and give the possibility to have a modification history.

Go to the review tab and click "Share this Workbook".
Give it a try first because shared workbooks have a lot of drawbacks.

Hope it helped

 

[wideboydixon]

Let's say your formula is in A1. Place the following formula in a cell somewhere on the sheet where you can hide it later:

=FORMULATEXT(A1)

Let's say you put this in Z1. Copy cell Z1 and paste special as "values". This gives you the formula in the cell. Now add a conditional format rule on cell A1 using a formula:

=FORMULATEXT(A1)<>Z1

Choose a highlight when the condition is true. Hide column Z and try changing the formula in A1 - it should highlight if you change it from the original value.

WBD

 

[Excelgovgeek]

Unfortunately that is not possible since the workbook contains tables.

 

[Excelgovgeek]

Thanks for the suggestion! For some reason I am missing something however. =FORMULATEXT is not available for me. Am I missing something?

 

[Excelgovgeek]

Forgive me, I see that this is available in Excel 2016. I attempted this on that version and it works beautifully! Thank you for the suggestion!!!!

 

[wideboydixon]

Cool. Glad I could help. I forgot it was only on Excel 2016 though ... :-|

WBD