Automatically Pick Value from Drop Down Based on Another Cell's Value

Question

I am not sure if this is possible. I am working on a tracker for license renewals where we have to submit documentation by certain dates and I want to track if that date is upcoming, but also what stage the process is in (ie if I have started to get the docs together, submitted them, waiting on a response, etc).

The tracker has an expiration date column and a column for status. I have a drop down in the status cell. I would like the status column to auto-populate with "Upcoming" if the expiration date is within 60 days or less. However, I also want to be able to manually change the status to "filed/waiting for confirmation", "documents submitted to counsel", or "requirements received/compiling documents" without it changing back to upcoming, even though the date doesn't change.

So for example, the 3rd row deadline is 12/1. I want it to auto populate with Upcoming since it's within 60 days. But then tomorrow I make the submission so I change the status to "documents submitted to counsel", but leave the date as 12/1. I don't want it to automatically go back to Upcoming after I change it. since the date is still the same.

Then once the process is complete I put in 12/1/25 as the new expiration date and change status to complete, I want it to once again change to Upcoming when we get within 60 days of the new date next year.

Hopefully that makes sense. I'm not sure if this is possible since it would be creating one rule, only to break it, and then still have it work the same later.

Answer 1

This requires VBA, so it will only work in the desktop version of Excel, not in the online version, nor on iOS or Android.

And users will have to allow macros.

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the code listed below into the worksheet module.

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Intersect(Range("C2:C" & Rows.Count), Target)
    If rng Is Nothing Then Exit Sub
    If rng.CountLarge > 1 Then Exit Sub
    If rng.Value = "" Then Exit Sub
    If rng.Offset(0, 1).Value <> "" And rng.Offset(0, 1).Value <> "Complete" Then Exit Sub
    If rng.Value > Date + 60 Then Exit Sub
    Application.EnableEvents = False
    rng.Offset(0, 1).Value = "Upcoming"
    Application.EnableEvents = True
End Sub