If nothing is selected from data validation list, then apply a formula.

[cwelsh]

I have a table with cells that are currently using the following formula: =IFERROR(IF(MATCH($B4,Roster!$J$9:$J$63,0),"Yes"), "")

I have conditional formatting in place depending on if the result is yes or no.

What I want is the ability to apply data validation where I can override the formula and select something like "Down" or "In Shop" from the list, but then if not selected the formula is there... So basically the formula is the default that could be overridden but always there it not...

Is this possible?

Thank you!

[Trebor76]

Hi cwelsh,

Assuming the data validation is in cell A1 (change the following to suit), this will do the job:

=IF(LEN($A$1)>0,$A$1,IF(ISERROR(MATCH($B4,Roster!$J$9:$J$63,0)),"","Yes"))

Regards,

Robert

[cwelsh]

Thank you but, it doesn't seem to work.

I have the formula in cell c4 and then i put data validation over that cell. When I click the drop down arrow to select an option it will fill it in but then when I delete the option, the formula is gone. I want the formula to stay.

[MatrixMan]

Use the Worksheet Change event. If the cell value is empty, write the formula; if a value has been selected, leave it.