Any way to have conditional default value in a cell while also can set the cell value manually

[weikun]

I want to have a cell being filled with some default value depending on another cell value.  - This is the easy part. 

Meanwhile I want to have the capability to set the cell value arbitrarily in a manual way.

So the cell shows up like displaying the default value when the excel sheet first opened,  and it can also be overridden manually. 

Any method to do that?

[#2]

@weikun 

May I suggest that you back up and describe more fully the context in which you're making this request. The context, what it's all about, why this seems to you to be "the way to proceed."

I ask that because what you appear to be describing could work once, but not repeatedly. Once you've overridden manually whatever is in the cell via a formula that references another cell--once you've manually entered a different value--that manual entry is what's in the cell. Period.

So tell us what the bigger picture is. There may be a way to accomplish the same objective, but via different means, but we need to know what the bigger picture is.

[#3]

@weikun ,

There's nothing stopping you overwriting the formula with an arbitrary value in a manual way.

So if cell B1 contained the formula:

=IF(A1="","Write your given name in A1 and last name here","Write your last name here")

You would initially see:

And once you'd entered something in A1, it would change to

And then you could fill in cell B1

The problem is, the text entered in B1 has overwritten the formula, and if you want the prompt back, you'll have to retype the whole formula again - there is no way of having the default formula restored unless you enable the spreadsheet for macros and write a macro - the one found here would do the job

https://answers.microsoft.com/en-us/msoffice/forum/all/excel-vba-code-for-default-value-in-cell/ca60...