Setting a default cell value

Question

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.

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. — Jack Archer
– Jack Archer, Commented Aug 6, 2015 at 22:36

pnuts · Accepted Answer · 2015-11-08 05:46:48Z

1

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.

answered Nov 8, 2015 at 5:46

pnuts

59.8k11 gold badges93 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Setting a default cell value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related