=IF formula to fill a referenced cell

[acass2024]

Hello All,

I am trying to make a fillable worksheet for internal use within our office.  I have a cell (B3) that I want to hold placeholder text that tells the user what to put into it.  I'm hoping I can make a formula to fill the cell with a text prompt when left blank.  In essence this is the "If/Then" statement:

IF REFERENCED CELL IS BLANK THEN FILL REFERENCED CELL WITH "NAME"

My thinking was to write an =IF combined with =ISBLANK formula in a separate cell (i.e. G30) that references B3, and then fills B3 with "Name" if it is blank.  The formula works but it understandable is filling G30) with "Name".  Does anyone have any ideas of how I can have the formula fill the cell it is referencing?

Additionally, I'm also anticipating a logic error with this.  If I have a formula checking to see if a cell is blank, and then filling that cell, it won't be blank.  Is that a problem?

[#2]

@acass2024 

Is C3 what you meant?  Just put a ISBlank(cell#)=TRUE inside the IF statement.

[#3 · Accepted solution]

so no you can NOT set the value or display setting of a different cell using a cell formula
you could use a macro/VBA but even that might get tricky/buggy
one option you could do is use custom formatting and define '0' to be "name" and then preset the value of those cells to be 0 (zero). In this way any formula could easily ignore anything = 0 but the user would SEE the text prompt
so to do this select cell B3 and go Home-> Number ->custom formatting->Custom -> and in the box type something like: 0.00;-0.00;"Name"

This is summarized in the following picture:

notice how cell H29 is selected and is SHOWING "Name" but in the formula bar the actual value in the cell is 0.  And the Format window is open showing that cell has custom formatting selected and in the "Type" entry I entered:    0.00; -0.00; "Name"

[#4]

@m_tarler 

Thank you!  The custom format idea is very clever.

Since you mentioned VBA, I figured I'd give it a shot and see if anyone else has tackled this problem.  I found this solution which is working wonderfully!  I altered the code to reference a single cell and fill with "Name".

[#5]

@acass2024 

Something you can do is write a formula or input text directly but format it to display within an adjacent blank cell by using 'Increase Indent'.  As soon as the display cell is occupied the overflowing text disappears.