HOw do i stop a Timestamp from changing when using NOW Formula when Cells are updated in excel

Question

I am inserting formula for =IF(C3<>"",NOW(),""), but i don't want it to change if excel worksheet is updated. I can't use shortcut CTRL+;, because of the users that will be utilizing this sheet. I am trying to track parts ordering so i want to measure the start to complete time frame.

Answer 1

Ok, here is some VBA code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub 

Application.EnableEvents = False 

Target.Offset(0, 5) = Now 

Application.EnableEvents = True 

End Sub

In this case the range where the formula will be entered is F1:F10, when the cell on the equivalent row in column A is selected. This may not be exactly what you need but it gives you an idea.

But for the macro you need we need to have more details. What cells or text do you want to trigger the macro? Where do you want the date to be entered?

Answer 2

Hi,

My VBA is rusty, but someone here can write you a short macro that will do the same thing I'm suggesting, converting the formula to a value. This would be what we call a Selection Change event.

Answer 3

What formula then would i have them Copy & Paste, the CTRL+; Space CTRL+SHIFT+; ? I am trying to have the timestamp auto-populate when text is chosen. trying to not have alot of user manual input. More automated

Answer 4

Hi,

Now always updates with the spreadsheet. You could have them copy the formula and then paste special values. This can be done from the Clipboard group on the Home menu, from buttons on the QAT, or shortcut keys: Ctrl+C, Ctrl+Shift+V. This last shortcut key was new in 2022, alternately Ctrl+Alt+V will open the Paste Special dialog box.