Assigning Value to a field only if their is a certain value in another field other wise the field should retatin its old value

Question

I am trying to come up with a formula field which gets updated if another field has one of the three values I have used in the formula. It should only change when its one of the three values otherwise remain the last accepted value. here i what I have so far

CASE( text(SOE_Order_Status__c) , 
          "On Hold-Pending NC Order", "On Hold-Pending NC Order", 
          "ON HOLD - TN Inventory", "ON HOLD - TN Inventory",
          "Pending NC Tbl.Tkt.","Pending NC Tbl.Tkt.",
          "")

basically if somebody changes it to some other value, other than what I have used in the formula, my new field should retain the last acceptable value. I know in my formula my else is saying to use blank but instead of that I wanted one of three values I have listed.

Answer 1

I would recommend using a workflow rule to update a text field vs using a formula, that way you can have it only execute when that field has changed and has changed to a value being monitored.