Populate date in cell when value selected from list in another cell

[meinblitz]

Hi all

Please forgive my ignorance, I am relatively new to Excel in this capacity and despite my efforts the find the answer online; I cannot seem to figure this out! Your help is greatly appreciated.

In column D I have a list of current job status. I need a formula to automatically populate the date each status was selected from the list into corresponding date columns "G, H, I, J & K".

For example, when the status "Complete" is selected in cell D2, populate the date in column J2.

I hope the image attached will help.

Thanks!

 

[mabbutt]

Hi all

Please forgive my ignorance, I am relatively new to Excel in this capacity and despite my efforts the find the answer online; I cannot seem to figure this out! Your help is greatly appreciated.

In column D I have a list of current job status. I need a formula to automatically populate the date each status was selected from the list into corresponding date columns "G, H, I, J & K".

For example, when the status "Complete" is selected in cell D2, populate the date in column J2.

I hope the image attached will help.

Thanks!

Hi Meinblitz,

This should do the trick for you:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim controlRng, nRng As Range
    Set controlRng = Range("D2")
    Set nRng = Intersect(controlRng, Target)

    If nRng Is Nothing Then Exit Sub

    If Target.Value = "Due Date" Then
        Target.Offset(0, 2).Value = Date

    ElseIf Target.Value = "Proposal Sent" Then
        Target.Offset(0, 3).Value = Date
    
    ElseIf Target.Value = "Approved" Then
        Target.Offset(0, 4).Value = Date

    ElseIf Target.Value = "Assigned" Then
        Target.Offset(0, 5).Value = Date
        
    ElseIf Target.Value = "Completed" Then
        Target.Offset(0, 6).Value = Date
        
    ElseIf Target.Value = "Signed Off" Then
        Target.Offset(0, 7).Value = Date
        
    Else
        'Do Something
    End If

End Sub

Update the controlRng to expand the cells that the Worksheet_Change function works for and add the code to the Sheet and not via a module.

 

[meinblitz]

I can't thank you enough!

 

[mabbutt]

No problem at all.