Insert Todays Date in cell when formula result changes

[fpt264]

Good morning,
I have a formula in cell M19.

When entries elsewhere in the sheet cause the value calculated by the formula to change I would like to insert the date in cell M21.

Thanks in advance for suggestions.

[Gatti]

Hello

Please Login or Register  to view this content.

[Pan314]

Dear fpt264


This might be a good solution!

Greetings Pan314

Please Login or Register  to view this content.

[fpt264]

Thanks for the replies but neither one works.

To be clear, cell M19 contains a formula the result of which is dependent on entries made elsewhere in the sheet. If an entry causes the value in M19 to change I want the date in M21 to update to the current date.

[Pan314]

Hi!

It works for me.
I will send the sample file.
If you have questions, you send a post.

Pan314

[fpt264]

Hi,
I tried it again and it does insert the current date in M21 however Excel then proceeds to crash and restart to the previously saved file. I have no idea why. I've tried it several times. I'm using Excel 2016.

I'm curious, what purpose does the "Range("M20000").Value " serve?

Thanks for your interest.

[Pan314]

Hi fpt264

The file I sent you, did not it work? Not at all or wrongly? I do not understand.
I have a 2007 version. You also should work with it. Send own files to me (not real data and formulas will be good). Today it is not certain that I'll have time to deal with it.

Pan314

[fpt264]

Hi Pan314
Yes your file works fine. I copy and paste the code in my file and after I make an entry that causes M19 to change the date and time appear in M21 then Excel crashes and closes without saving changes. I'll see if I can make a small test file to show you. No hurry. Thanks for your interest.

[Pan314]

Postscript: M20000 includes the value calculated for the last time in cell M19. (Any cell can be used instead of M20000)

[Gatti]

@fpt264

By the way: you have to put this code in Worksheet VBA Page

[sourabhg98]

Don't know exactly what's causing the error.
You can store value internally using the macro below. Try if that works-

Please Login or Register  to view this content.

RIGHT CLICK ON SHEET NAME >> VIEW CODE >> PASTE THE ABOVE MACRO

[Pan314]

Hi fpt264!

I was on the wrong track. The winning Sourabh G98. His better. Press a button * Reputation him.
Press the button * Reputation him!

Good luck with VBA

Pan314

[fpt264]

sourabhg98,
I tried your code but it is not working. I have attached a sample file. In this case when H14 changes the date should be recorded in H15. Thanks for your interest.




Sheet1.xlsm

[Pan314]

Dear fpt264!

Still, it's my solution looks good. The fact is that the formulas recalculate not cause Worksheet_Change event.
Into the file named Sheet1-2 I copied my code. Now I am sending this to you as Sheet1-2. Works well. Try.
Yesterday I was wrong regarding sourabhg98 solution.


Pan314

If that does not work with you again, it will be the big crossword puzzle.

[sourabhg98]

oops, By mistake pasted the wrong macro--
Try this one-

Please Login or Register  to view this content.

[fpt264]

Pan314,
Your latest version works fine, as does the one offered by sourabhg98. I have two sheets so I can evaluate them side by side and see if there is any advantage to one over the other. Thanks for all you help.

fpt264

[fpt264]

sourabhg98,
Your latest version works fine, as does the one offered by Pan314. I will evaluate them side by side and see if there is any advantage to one over the other.

I appreciate you help.

fpt264

[Pan314]

Hello fpt264!


The first was my solution is the same. For some reason it did not work in your place.

Sourabhg98 code is also good, even elegant, because the old value is stored as static variable. For this purpose, I used a cell awkwardly.

Good for the future is worth noting the nature of static variables.

Pan314

[fpt264]

Hello Gatti,
This is a belated thank you for your response to my post. It seems I initially overlooked your solution which at this point seems to be the best one. With the other suggestions the date was updated every time I opened the sheet, not just after
entering a change.