How to implement in Excel: If cell A1 is 0, cell A2 = "foo". Else, let the user specify an input.

Question

It's pretty straight forward. If a certain cell, such as A1, is the value "0", I would like A2 to contain an arbitrary value. Otherwise, I would like the user to be able to enter their own value. The first part is easy:

A2=IF(A1 = "0", "value", "")

However, if A2 is empty, and the user goes to edit the value, Excel presents the formula itself to be edited.

Is there any way around this?

Answer 1

A little different approach you need to create custom number format instead of a formula. Use something like this #.0;#.0;"Value" then copy the value from A1 to A2 and use the custom number format on A2.

Answer 2

I generally set up an override sheet. So that if there is an override by the user, the formula reads it, otherwise it uses an if statement to evaluate to another result.