I've got a spreadsheet with cells that I want to be user-editable, but that I also want to have "default" or "placeholder" values in, whenever there is no user-entered data. There's a couple good use cases for this:
- Prevent formula errors, while providing reasonable assumptions when a user has not entered (or has deleted) their own value.
- I could use conditional formatting to alert the user to default values, so as to prevent their ignorance of them - they can then make an informed choice as to whether that value is still appropriate or not for the intended calculations.
- Give a short description of what is intended to be entered in the cell, without having to have a separate "instructions" segment or document. This would also eliminate the need for a nearby "Label" cell, in some cases where it's really not appropriate.
To accomplish what I want, I need some formula, script, or other advanced spreadsheet option that will do the following:
- Show the default value in the cell before user enters data.
- Allow the default value to be found by any formulas referencing the cell, when there is no user-entered data in that cell.
- Allow the user to freely (naturally, exactly as they would do with any "normal" cell) overwrite the displayed value with their own value or formula, and have the user-entered data found by any formulas referencing the cell.
- When cell is blanked by deletion of user input, revert to default value.
Is there a way to do this in Excel, or am I asking too much of a spreadsheet program here?