how to get static timestamp - code/formula

[Vishaal]

Hi all,

We have the following excel sheet

Book1
	A	B	C	D	E
1	S.No.	Drop/Down	TimeStamp		Rock
2	1				Peter
3	2				Hileri
4	3				Danial
5	4
Sheet1

we want that when we choose any drop down than we get a static timestamp cell, can we do it with arrayformulla

 

[JLGWhiz]

Probably need event code to make it static. Put this in the worksheet code module for that sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
    Application.EnableEvents = False
    Target.Offset(, 1) = Now
    Application.EnableEvents = True
End If
End Sub

 

[Vishaal]

Thanks JLGWhiz, can we do it through a formula or code

Not vba

VBA is work in my case, but if we can get the formula its good for me

 

[Joe4]

Thanks JLGWhiz, can we do it through a formula or code

Not vba

VBA is work in my case, but if we can get the formula its good for me

I am confused. You said "formula or code" is OK, but "VBA" is not. "Code" is "VBA"!

Note that formulas will not work if you want a static timestamp. You have to use VBA, unless you want to manually Copy/Paste Special Values on your =NOW() formula every time.

 

[Vishaal]

Ok,

code is vba, u r right and sorry for my subject and post description

 

[JLGWhiz]

Ok,

code is vba, u r right and sorry for my subject and post description

Like @Joe4 said, formulas will not return a static value. They react to whatever action happens in the dependent or precedent cell. With the event code placed in the worksheet code module, it will react to changes made on the worksheet, and in this case, only execute the time stamp when the change is in column B dropdowns. What I did not know was if the time stamp is related to the names or the cell with the dropdown. It would make a difference of where the timestamp appears in column C.