fill/copy and then keep value from changing

See attached sample::

Col A = date
D2 changes value each entry date

Col B = D2 [value] on the date entered
then retains this value when date & D2 changes

Example: see attached

On 3/3, D2 = 45, then want B3 to = 45

On 4/18, D2 = 25, then want B4 to = 25, and B3 = 45, [B3 retain original value]

On 6/28, D2 = 100, then want B5 = 100, B4 to = 25, and B3 = 45, [B3 & B4 retain original values]

On 7/28, D2 = 200, then want B6 = 200, B5 = 100, B4 to = 25, and B3 = 45, [B3,B4,B5 retain original values]

On next entry date, D2 = XX, then want B7 = XX, B6 = 200, B5 = 100, B4 to = 25, and B3 = 45, [B3,B4,B5,B6 retain original values]

You will need VBA for this. Shall I move your thread for you?

How do you know?

Thread moved back to functions and formulas section.

As already told this is not possible with formula. here is the worksheet event code.

Please Login or Register  to view this content.

Worksheetevent

How to use the code
Right click on Sheet tab --> view code
Visual Basic (VB) window opens.
Paste the code
Close the VB window.
Save the file

As already told this is not possible with formula. here is the worksheet event code.

He didn't believe me ...

Possibly he may be having more faith in formula.

kvsrinivasamurthy,
Thank you.

My Excel formula and code knowledge is very limited and primitive. My queries are not to challenge, only to learn.

1) Why do you have "Application.EnableEvents = False"? Please give specific need.
2) Your code responds for any change as specified and is not TODAY() specific. Can it be made to be run only if the current date is entered.

AliGW & kvsrinivasamurthy:
Why is this request/task not viable with a formula?

Formulae always update. In a cell, you can have either a dynamic formula or a static value, but not both. Excel has no way of turning a formula cell into a static value based on the date without VBA.

There is a way to create a time stamp in Excel, but it would not work for what you want, which changes each day in a sequential way.

It’s VBA or nothing, I am afraid.

Try this code

Please Login or Register  to view this content.

Post#12
yes, it is necessary. It prevents triggering the event when changes are done in cells due to the event program.

When program is running if the value of a cell is changed by the program that should not initiate the program once again.
This line, if target is A4 chnanges the value of B4 . This event should not run the program once again
Target.Offset(0, 1) = [D2]

It cannot be done with a formula.