Option to switch between formula and manually entered value in a cell.

**lgass** · 01-28-2014, 03:16 PM

I'd like to use a formula in a cell, but let's say someone has come along and manually entered a value in that cell (and clicked 'Save'), I want to be able to revert that cell back to it's original formula without having to physically re-type the formula in the cell. Is this possible?

For example: I made a sheet with a bunch of formula's. My manager comes along and changes one of my cells (which contains a formula) to an arbitrary value, e.g. 20. He then saves the file. A few days later he realizes his input of 20 is not as good as the formula's original answer, and wants the formula to do it's job as it did before. How can he do this, without asking me to re-enter the formula manually?

I basically want to make my sheet dummy-proof. I thought of locking formula's, but that would prevent my manager from making any changes to the formula cell at all. I want the flexibility of allowing him to input values, as well as revert back to the formula, without asking me to re-enter the formula.

If this is possible - please help! Very much appreciated.

**MrShorty** · 01-28-2014, 03:39 PM

The way I would do it would be to have a separate "manual override" cell where you manager can enter arbitrary values. Then you can nest your formula into an IF function that will test the manual override and behave accordingly. For example, formula is in C4, and I assign B4 as the manual override. C4 would have a formula like =IF(ISBLANK(B4),your formula,B4). You can now protect C4 so it cannot be edited, and you can teach your manager to enter his arbitrary values in B4 when he wants to use them, and that he can just delete them from B4 to revert back to the original formula.

**etaf** · 01-28-2014, 03:41 PM

how complex is the sheet
can you make the sheet so that those entries can be either blank or a value
and then use the formula
=if( cell ="" , your formula , use the number entered)

i appreciate if he is doing this across a complex sheet , its not going to work - but if its one column or 2 columns hes likely to change
then a clear would use your formula and data entered would be his
you then dont need to do anything

**newdoverman** · 01-28-2014, 04:20 PM

Lock all cells with formulae and text leaving cells with shading unlocked where comments or other entries could be made then protect the worksheet.

I had people including a boss, who just couldn't leave formulae alone and overwrote them. My only defence was to lock them out of areas where they shouldn't be. I got away with it because I developed the workbooks and was the only Excel resource available to them.

You might want to experiment with Track Changes found on the Review tab so that you can see what was changed and accept or reject the changes.