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

meinblitz

New Member
Joined
Feb 8, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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!
 

Attachments

  • excelimage1.jpg
    excelimage1.jpg
    89.6 KB · Views: 3

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

mabbutt

Board Regular
Joined
Oct 4, 2014
Messages
99
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:

VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,583
Messages
5,625,623
Members
416,124
Latest member
DeMoNloK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top