Formula Question

[omgitzchris]

I know there is a way to do it in VBA code and many other ways.
But my issue is it automatically updates.

I have 4 collumns:
Location//S-1//CSM//SCO
I want to enter in the location in a drop down list, which I know how to do. The thing I'm trying to do is when the Location says To S-1 for example, the date that it is changed "To S-1" the collumn under S-1 will post the date. When location changes to CSM, it will put that day in that collumn, etc.

My issue is that if I use the NOW() or TODAY() function in an IF() then it will update evertime I reopen the file. So the dates won't stay fixed. So if I change the Location to S-1 on the 12th, open it again on the 13th... it will say the 13th not the 12th.
Make sense?

Any help will be great. I have a time line so the quicker the better.

Thanks once again.

 

[omgitzchris]

More info:

=IF(B3="To SCO",NOW(),"N/A")
=IF(B3="To SCO",TODAY(),"N/A")

Those are the formulas that I was using, but is there a way to preserve the dates once they are entered into the field?

 

[omgitzchris]

Can anyone help me with this?

 

[Richard Schollar]

Hi

You need to use Event Code (you can't do it via formula). Are you using Data>Validation for the dropdown list?

 

[omgitzchris]

Yes I am.
I named the To CSM//To SCO//etc as "status" and used Data Validation List to =status.

 

[Richard Schollar]

Hi

Here's some code - right-click on the sheet where you enter these values and select View Code - paste the following in there. Note it assumes that your four columns are A:D and that you have a header in row 1. You may well need to amend these columns to the actual range you have.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
On Error GoTo err_handler
If Not Intersect(Target, Range("A2:A1000")) Is Nothing Then
    Application.EnableEvents = False
    With Target
        Select Case Target.Value
            Case "To S-1"
                .Resize(, 3).Offset(, 1).ClearContents
                .Offset(, 1).Value = Now
            Case "To CSM"
                .Resize(, 3).Offset(, 1).ClearContents
                .Offset(, 2).Value = Now
            Case "To SCO"
                .Resize(, 3).Offset(, 1).ClearContents
                .Offset(, 3).Value = Now
            Case ""
                .Resize(, 3).Offset(, 1).ClearContents
        End Select
    End With
End If
err_handler:
    Application.EnableEvents = True
End Sub

 

[omgitzchris]

Richard,
You are awesome. The one thing however, is I need to change it to where it will perserve the dates. For example, if I change the location, the previous location's date erases. Is there a way to fix this?

Thanks for your help.

 

[Richard Schollar]

Oh yes of course - sorry, for some reason i thought you would want this functionality! Amended code is:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
On Error GoTo err_handler
If Not Intersect(Target, Range("A2:A1000")) Is Nothing Then
    Application.EnableEvents = False
    With Target
        Select Case Target.Value
            Case "To S-1"
                .Offset(, 1).Value = Now
            Case "To CSM"
                .Offset(, 2).Value = Now
            Case "To SCO"
                .Offset(, 3).Value = Now
            Case ""
                .Resize(, 3).Offset(, 1).ClearContents
        End Select
    End With
End If
err_handler:
    Application.EnableEvents = True
End Sub

If you delete the value in column A, it will still clear all dates in B:D. This can be easily amended if you require.

 

[omgitzchris]

Richard you are effing awesome.
Now, can this code be applied to a Mac running Office 08 as well or no?
lol.

 

[Richard Schollar]

Was that a serious question? MS killed VBA in Mac Office2008 (I wonder which marketing genius made that decision?) so it won't run under Office2008 I'm afraid.