Data Validation with default option

[DavidBW]

I would like to have a cell show a default value when text is entered into another cell on the same row with the user having the option to overwrite the default text with a choice from a data validation drop down list.
Currently I have the following code in C15:

=IF(A15<>"","No particular customer","")

In C15 I have the data validation option. The problem occurs when a value is selected from the list therefore erasing the formula. This means that if I want to use the file for another user or period then the formula has to be recreated.

Thank you

[Palmetto]

Cells can only contain a formula OR a static text/numeric value. Unfortunately you cannot have it both ways. Data Validation presents a static value from a selection list (or limits input some other way).

You could use a bit of VBA that presents the user with the option of selecting from a drop down and, based on response, either inserts the formula or allows the selection. However, VBA is not a full-proof solution.

[DavidBW]

Thank you for your response. I'll go for a simple Data Validation list with "no particular customer" being the first choice.

[Bali Rakhra]

Just figured out a workaround to this annoying limitation. You could create a very thin column just to the left of the column which houses the dropdown list, and in this thin column, enter your default message in the cell to the left of your dropdown cell.

Due to the way Excel displays text that is larger than the cell that holds it, this text will still be readable 'over' the adjacent cell while the adjacent cell is empty. But as soon as you select one of the options in the dropdown, it will fill that cell with a value, causing the text in the left cell to disappear from view and giving the appearance of a default value functionality.

If you can't create the thin column to start with, you should still be able to do this if the left or right cell is unused. If the left cell is unused, just add a couple of spaces to the beginning of your text (to shift it to the right). If you can't use the left cell but the cell to the right of the dropdown cell is empty, you can enter the text there, align the text to the right, and add some spaces to the end of the cell (followed ultimately by an actual character eg a dot or comma etc (which is best to change to white font colour (or other colour that matches the cell background) so that it blends into the background, and therefore also rendering it invisible to the eye and completing the illusion of functionality!)