Keep values from source cell even if source cell is deleted

Hi everybody.

I´m working in a workbook to keep tracking of the changes in the clients demand dates.

I have a initial date (from) and end date (to) and i need to store this dates when they are deleted.
I was able to come around a way to store the values and if the source change, the cell keeps the original value and a cell next to it keeps the new date, and so on, up to 10 changes.
I also record the date and time the change was made
But if i delete the source, everything is lost.
I need to figure out a way to do keep the stored values even when i delete the source.

Example of my sheet:

B2: source start date (from)
C2: source end date (to)

hidden collumns:

D2: initial start date =IF($B2="","",IF(D2="",$B2,D2))
E2: initial end date =IF($C2="","",IF(E2="",$C2,E2))
F2: 1st input =IF(E2="","",IF(F2="",NOW(),F2))

Changes:
start date 1: =IF(D2="","",IF(D2=$B2,"",IF(G2="",$B2,G2)))
End date 1: =IF(E2="","",IF(E2=$C2,"",IF(H2="",$C2,H2)))
Change time 1: =IF(H2="","",IF(I2="",NOW(),I2))

and so on up to change 10

Does anyone have an idea?

Thank you very much!!! Its exctaly what i needed!!
I wanted to delete the cell and keep the values.

Only one minor thing, when i delete the source cell, it marks as a change. Is there any way to avoid that?
Im not a VBA expert

Originally Posted by dflak

What do you mean by i need to store this dates when they are deleted. Do you mean you select the date cell and press the Delete key? Do you mean delete the whole row? Also you mentioned that you want to keep the last 10 changes.

I think I have something that might work. However if you write the information to a log file you can have an indefinite number of changes, and record a change time for both the From and To.

This workbook uses a VBA change event.

If i write to a log file, can i extract later in another sheet by formula?

Another, is this VBA gonna work for every row or just 1? in the example, i have just one row, but in the real workbook i will have infinite rows, so i need record each one of them.

Originally Posted by rjcarlantonio

Another, is this VBA gonna work for every row or just 1? in the example, i have just one row, but in the real workbook i will have infinite rows, so i need record each one of them.

Never mind, i just checked and it works for the entire row

Originally Posted by dflak

A couple of things:

I can add a check so if the target is blank, it does not mark it as a change.

I converted a range to an excel table. This means that everything copies down as you add rows. It also limits the number of cells that the code has to look at.

If I change the code to write to a log file, then you can recover the contents with a pivot table.

Would you like to investigate doing a log? I'll make the other fixes when I convert to this format.

If you could, it would be awesome!!!!

Man, This was a hell of job!! Amazing.
Thank You very much, it works perfect