IF Statement help please?

[apearson]

Hi All veiwing members,

Can someone help with my IF statement as shown below please:-

=IF(A10<>"",IF(G3="",NOW(),G3),"")

When data is entered into 'A10' then 'G3' records the date/time.

My problem is, I want 'G3' to record the date/time if data is entered into any cell from 'A10:A200', but just can't find anything that works?

Also does it matter if data is entered into more than one cell in Col A?

Using Excel 2003

Thank you

Alley

 

[Richard Schollar]

What's your ultimate goal here? Since you are using a formula to return the date, it will update to whatever value NOW() is each time the cell recalculates (or change to whatever value is in G3 if that is changed). Do you want the time/date to continually change?

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Intersect(Target, Range("A10:A200")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each c In Target
    If c.Value <> "" And c.Offset(, 6).Value = "" Then c.Offset(, 6).Value = Now
Next c
Application.EnableEvents = True
End Sub

 

[apearson]

Thanks VoG & Richard,

The reason for the question is:-

I have a template for work amendments that people send to me, which contains a date field, but it is often not filled in.

The start cell for them to complete the template should be cell A10.

Should they choose to miss out row 10 & start to enter data in A11, I still want the date/time to be recorded, as quite often the amendment requests only needs one row
of my template.

Hope this helps

Alley

 

[VoG]

My code should work for that.

 

[apearson]

Hi VoG,

Your formula does work, only it puts the date/time in the adjacent cell in Col G.

Whereas I just want it to record into cell G3.

I tried to alter the formula you gave me, but I failed miserably, sorry.

Could you help me out here please?

Alley

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Intersect(Target, Range("A10:A200")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each c In Target
    If c.Value <> "" And Range("G3").Value = "" Then Range("G3").Value = Now
Next c
Application.EnableEvents = True
End Sub

 

[apearson]

VoG,
I do appreciate your time, but I'm afraid that doesn't work at all.

Alley

 

[VoG]

Works for me.

 

[apearson]

Hi VoG,

OK thanks I'll give it another whirl & get back to you.

Alley