Formula clears in the data validation cells

[dineshsachidananda]

Hi Experts

Requesting your help in the below query

I am trying to validate if a cell is blank with a 'if condition+data validation'. However sometime I have to overwride the formula, and change the value of the validation manually. However when I do that the formula get cleared in the cell.


I need help in retaining the formula still in the cell.. Attached is the test sheet.. Looking for your assistance.. thanks in advance.

[jason.b75]

You can't change the formula and still have it.

[dineshsachidananda]

Hii Jason... thanks however is this possible through VBA

[jason.b75]

If you overtype the formula then the formula is gone, if you use any other method to retain the formula when it is deleted then it will immediately replace the override value with the original formula and return to the original result, leaving you in an infinite loop.

The only possible way out is to use multiple columns, one for formulas, one for overrides, then probably a third column =IF(override<>"",override,formula) to use with any formula that needs to look at the results of the whole column.