Excel 2007 : Keep Cell Formula After User Hits "Delete" Key

**Kingswood** · 08-06-2011, 12:31 PM

My cell formula, =IF(OR(B61<>"",C61<>"",D61<>""),"When Approved, Enter Commit Date","") appears to be working fine. I get the message in the cell when criteria is met. And I have a data validation on cell M61 to ensure I get a good date for that cell. When a bad date is entered into cell M61, the validation works perfect and the User is prevented from entering a bad date. The problem is:

When a User hits the "DELETE" key, the formula message is erased. Or, when a User enters a valid date and then decides to delete the date (using DELETE key), the cell is now empty and no longer displays the formula message. My intent is to either have my formula message in the cell, or, have a valid date in the cell. Can someone help?

**DonkeyOte** · 08-07-2011, 02:52 AM

You can't really mix formulae & constants without interjecting some VBA.

I would thus suggest either moving the in-cell formula notification to an adjacent cell or modifying the DV rule such that it permits only a valid date when your existing IF is satisfied, e.g:

Please Login or Register  to view this content.

the above simply states that M61 entry must be within the last 30 days and that none of B61:D61 are Blank/Null.

It should be noted that the Validation rule is only ever applied at point of entry. For ex. were B61:D61 non-blank and M61 assigned a valid date and the user then deletes content of B61 this would not force M61 to then flag as invalid.