Excel enter date by value of dropdown list

Question

I am making a excel sheet where I have a dropdown list (just a data validation list) containing the status of a project. I would like to fill multiple cells with the date that one of the drop option is clicked. So for example when the status is changed from Ongoing to Finished, the date in the column "Date finished" is automatically filled with the current date and time.

I currently have the following formula in the cells that need to get filled in automatically:

=IF(ISNUMBER(SEARCH("Ongoing", A2)), NOW())

Using this formula the date does not "freeze" in the cell and when you choose another status the date is removed from on cell and updated to another one.

What would be a straightforward approach to this problem? Thanks in advance.

Answer 1

Here is a small demo that you can adapt to your needs. In the demo the user updates state in cells A2 through A10. If the user enters Finished, the macro will enter a static date in the adjacent cell. Enter the following in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim RangeOfInterest As Range, r As Range, rng As Range
    Set RangeOfInterest = Range("A2:A10")
    Set rng = Intersect(RangeOfInterest, Target)

    If rng Is Nothing Then Exit Sub
    Application.EnableEvents = False
        For Each r In rng
            If r.Value = "Finished" Then r.Offset(0, 1).Value = Date
        Next r
    Application.EnableEvents = True
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!