CODEWITHSUNDEEP
google-sheetsformula
Jack Archer
Jack Archer

Reputation: 21

Setting a default cell value

Is there a way to 'lock' a cell with data / formulas already entered in such a way that it is able to be altered but when you press delete or backspace it reverts to the previously entered 'locked' content.

I need this as some cells I have entered need to contain complicated formulae as well as be there own input for other cells.

(Attempting to create Rota's for my employer that calculate breaks etc on there own but I can't use other software.)

Edit: - I can't use protection because I need the cell to be edited if needed and then revert back to the formula if it is no longer needed for manual input.

Upvotes: 2

Views: 13822

Answers (1)

pnuts
pnuts

Reputation: 59475

You can't have a value (manual input) in a cell at the same time as a formula. If it has to be a singe cell then this would require a script - to detect a change of some kind and switch between accepting manual input and reinstating the required formula. However a similar effect can be achieved with two cells, say C5 (manual input) and D5 (formula). Then elsewhere: 

 =if(isblank(C5),D5,C5)

If a value is entered in C5 the formula above will take that value. If C5 is then blanked out (its content deleted) the formula above will resort to the value computed by the formula in D5.

Upvotes: 1

Related Questions