MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Temporarily overriding a Formula

Posted by Marion on January 23, 2002 7:51 AM

Hi -

So many questions today -
You are all saving my life as I'm trying to learn Excel on the fly
for a one-day project!

In Quattro, if I want to temporarily override the formula in a cell,
I simply place the value before the formula, and add a semi-colon before the formula.
To go back to the formula, I simply edit out the temporary value and the semi-colon, and voila, there
is my formula waiting for me.

In Excel, it seems that I have to:
A> create a comment as a holding place for my formula.
B> place the value in a separate cell, and create an IF statement in my formula
(so that the answer is either the value from the separate cell, or the value of the formula)

Is that true?
Any other alternatives?

Thanks so much -

Posted by Joe Was on January 23, 2002 8:04 AM

That's about it. I put a " ' " infront of the formula, Cut it and open Data-Data Validation-Input Message and Paste the formula as text into the comment. Or,

=IF(A1="x",your formula,your value) Note: A1 will never = x, unless you make it so. This is a soft switch. You can also make the test dynamic with the worksheet. To change the value, do so from the formula bar not the cell. JSW

Posted by Marion on January 23, 2002 8:13 AM

JOE: I did a variation on your suggestion

I'm having the value in A1.

the formula is now: IF(a1>0,a1,formula)

So, if my friend wants to override the formula, she can put
the value in A1.

Otherwise, the formula will take precedence.