Is it possible to have a formula in the column that only calculates the value of the column in case a certain criteria is met, otherwise it lets you add the value by yourself?
I have often desired that exact feature. Currently, as far as I understand, the simplest solution is to use the Value for new rows formula.
Obviously that only works for new rows, so if you need it on existing rows there are a few work-arounds, all with caveats:
- Automation: create a rule that modifies the row(s) in question whenever the criteria are met. Only problem is that this option is slow and users may be surprised when their values are over-written suddenly.
-
Use two columns: one column will be user-editable, and the other will be a formula that looks something like this:
If([criteria], [calculated value], [user-inputted value])
Then you have to figure out how to display it nicely to the user.
(You may consider adding conditional formatting to, for example, color the text of the user-editable field white (ie invisible) when criteria are met.)