Maintaining formulas in the cell

New Contributor

How do I keep this message (formula) in the cells after editing-Deleting values in the cells

Staff Loan Enter Amount 
Staff Mortgage Enter Amount 

The formula is =IF($G$4=F8, 0,"Enter Amount") and =IF($G$4=F9, 0,"Enter Amount")

3 Replies

If you let them type in that cell it will overwrite the formula. It appears the point of the formula is to prompt them to "Enter Amount" if the cell $G$4 above says it is a "Staff Loan" or "Staff Mortgage". But if that is the case I'm not sure why if the case is TRUE you enter 0 and false you prompt them. Maybe it is a typo or you are checking the opposite case or something. Any case, here is a little trick you can use for this case:
1) INSERT new Column in between
2) Put the formulas into that new column
example: =IF(($H$4=F8)*(H8=""),"Enter Amount","")
basically add to your condition AND the cell to the right is blank (or maybe OR in your case?)
3) Make that column Very Narrow so the Text spills over into the cell you want them to enter a value.
4) You should also Lock the Sheet so they don't accidentally modify or click into those cells you don't want changed.



EDIT: Example picture added and file attached

Thanks @mtarler 

Sorry for the confusion, I wanted a formula that will display Enter Amount when cell H8 when H4 is not staff Loan, display H9 Enter Amount when H4 is not Staff Mortgage and display Enter Amount when H4 is Not overdraft.

Your assistance will be highly appreciated

best response confirmed by David_Kahuhu (New Contributor)

@David_Kahuhu OK so i changed the formula to:

=IF(($H$4<>F8)*(H8=""),"Enter Amount","")

see attached.  But you can make the formula anything you want.  My real point was to show you how you could use this 'trick' to have that text appear to show in that cell.