[Solved] Enter Dates to Automatically Track Status of Event/Task

[ExcelGuy123]

Good Day,

Please see attached file. I have been working on this for days with multiple formulas and strategies but have not been successful.

GOAL:
Want to add dates to the beige cells in columns F, I, L, O, R, U and X.

Every time you add a date/value in column R (consecutively), for example, it will pull the corresponding value from Cell Group A10:A31, and place it in A1,2.

I tried to use a combination of INDEX and MATCH commands with looking for the MAX date, BUT, if you have equal consecutive dates, it pulls the previous date and note the most current cells.

In other words, if Cell F3 and F4 are 09/01/23, then it pulls text from Cell A10 only. What needs to happen is that if the dates are similar, it would pull the text from Cell A11 instead, because F4 is now complete.

The concept is that this is a tracker to keep track of where we are in a process. Cell A1/2 would be automatically updated based on the value of the last/most updated cells placed in columns F, I, L, O, R, U and X.

------------------------------------

Thank you so much for your help.

 

[Seemanto Saha]

Hello ExcelGuy123,
Thanks for sharing your problem with us. I understand that you want to add dates to the beige-colored cells in columns F, I, L, O, R, U, and X and put the corresponding value to the merged Cell A1:A2.

The Excel formula (combination of INDEX, MATCH, and MAX functions) you used has a few limitations such as the lack of automation, hard-to-maintain data entering sequence, and error in pulling data when consecutive dates are equal.

We can resolve all these limitations by executing a VBA code.

Open your required sheet >> right-click over the sheet tab >> select the View Code command.

A window like the following will appear.

Insert the following code in the module.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim range_monitor As Range
    Dim label_cell As Range
    Dim check_sequence As Boolean

    Set range_monitor = Union(Range("F2:F5"), Range("I3:I5"), Range("L5"), Range("O4:O5"), Range("R4:R5"), Range("U4:U5"), Range("X4:X5"))
    check_sequence = False

    If Not Intersect(Target, range_monitor) Is Nothing Then
        For Each cell In range_monitor
            If Not Intersect(cell, Target) Is Nothing Then
                check_sequence = True
                Exit For
            Else
                If cell.Value = "" Then
                    MsgBox "Please fill the previous cells first"
                    Exit Sub
                End If
            End If
        Next cell

        If check_sequence = True Then
            Set label_cell = Target.Offset(0, -1)
            Cells(1, 1).Value = label_cell.Value
        End If

    End If
End Sub

Note: Don’t forget to Save your workbook as an Excel Macro-Enabled Workbook (.xlsm) file.

Now, return to the required sheet and start entering dates in the beige-colored cells. The following link will anchor you to a video that demonstrates the output of the VBA code.

Overview of Automatically Tracking Status Of Event/Task

From the video, you can see:
1. For the beige-colored cells where dates are entered, corresponding data is pulled into the merged Cell A1:A2.
2. Even if I enter the same dates in consecutive cells F4 and F5, the merged Cell A1:A2 always shows the latest corresponding value.
3. If I skip any beige-colored cell(s) while entering dates, an error message will appear and no data will be pulled in the merged Cell A1:A2. This helps users to maintain the sequence of entering data.

I hope the above solution will be a suitable remedy for your problem. Let us know your feedback. The workbook used for this solution is attached below.

Regards,
Seemanto Saha
ExcelDemy