How to lock the result of a formula driven cell from the formula

[JMeehan]

I have a job tracking workbook set up in Excel with a formula that when the status is changed to "Ship Date" by entering an "s" it enters the date. I am attempting to add this for "Completion Date" by entering "c" as well. I have attempted this by copying the formula for "Ship Date" and modifying it, I need to keep the value of the "Completion Date" when it moves to "Ship Date", currently it takes the value away when "Ship Date" is entered. Results that I have found so far on the web require manually copying and pasting to retain the value, I would like to find a way that does not require extra manual input as others will be using this. I would like to modify the formula if possible but am open to any answer that makes this work. Please see attached sample, thank you in advance for any help that you can offer.

Joe M.

[PaulM100]

You will need VBA for this. Also, your second part of formula is doing nothing and should be removed. If(B2<>"" since is in the same cell

[PaulM100]

Something like this

Please Login or Register  to view this content.

it needs to be added in the worksheet module

[JMeehan]

Thank you for the information, I will see if I can make this work.

[MrShorty]

I don't know that VBA is necessary for this. The time stamp formula in column B needs a couple of extra conditions to test -- so that it can know what to do when A is "c" and what to do when a is "s".

Something like =IF(A2="c",IF(B2<>"",B2,NOW()),IF(A2="s",IF(B2<>"",B2,""),"")) seems to work. If someone enters "c" into A2, the time stamp will be created in B2. If someone subsequently enters "s" into A2, the time stamp will be retained (and a new timestamp created in column C from that formula). If someone clears A2 or enters something other than "c" or "s", then B2 (and C2) will both be empty string.

Edit to add: this is all assuming like every time stamp example like this, that you have enabled iteration.

[JMeehan]

MrShorty,,

This was the answer I was looking for, thank you very much to both you and PaulM100 for your time.
I had attempted something similar earlier, I am an armature and probably didn't have it typed quite right.

Thanks again,
Joe M.