Data Validation List - Auto update cell values with a twist

[Fritz23]

I know this question has been asked all over the web, but I have a unique case that hasn't been referenced elsewhere from the research I've done and could use some expertise.

I have a spreadsheet based on the data/columns below. The idea is to allow an individual to build an initial schedule based on various actions items, but the primary dates/anchors are based on events (i.e. build the initial schedule, and then use the built schedule to produce dates when this scenario of events and action items need to be run again).

When someone chooses "Event" from the Type drop-down/DV list, they then enter an event name in Column B (Event Name), and the date in Column E (Date). They can then reference that event in Column C (Connected Event) which is another DV list that reads from a helper sheet - the helper sheet does a VLOOKUP for all rows with the Event type, and the concatenates the event name and date. They can then enter a negative or positive number for other actions/rows under Column D (Days Before/After) which will automatically calculate the date of that action item.

I have a named range that I'm using to produce the DV List for the Connected Event column.

The problem is that event dates will shift, and DV lists don't automatically update cell values, meaning that the date calculations won't automatically update, requiring the user to manually update the Column C's connected events to calculate the new values. I've seen some VBA references to auto-update the cell values, but these all assume that the original DV list is being manually updated by a user, and isn't being produced by formulas..

In my case, the DV list is produced dynamically through the named range, and the following formula in column C of the helper sheet:

=IF(A1="","",CONCAT(A1,", ",TEXT(B1,"mm/dd/yyyy")))

I have not found any examples of how to detect a value change and then have the DV List cell values also update automatically. I am open to any/all suggestions.

Type (DV List)	Event Name	Connected Event (DV List)	Days Before/After	Date
Event	Some Event			1/1/2023​
Event	Some Other Event			7/15/2023​
Some Action		Some Other Event - 7/15/2023	2​	7/17/2023​

 

[Fritz23]

In case anyone else has a similar issue, I ended up with the following VBA which gets the job done. This is obviously specific to my use case and formula cells, but I've included some comments for background:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim keyCells As Range
    Dim oldEventValue As String
    Dim newEventValue As String
    
    Set keyCells = Range("_EventDates"): Rem cell with formula
    
    'The below checks for direct precendents of the columns that need to be monitored for changes since it's the parent column being monitored for changes, which is based on a formula
    Set keyCells = Application.Union(keyCells, keyCells.DirectPrecedents)

    On Error GoTo ErrorHandler
    
    'In my case, I was only looking for changes in column 4 or 8, which ultmimately would modify column 10 since column 10 was a fomula column combining column 4 and 8
    If ((Not Application.Intersect(Target, keyCells) Is Nothing) And ((Target.Column = 4) Or (Target.Column = 8))) Then

        oldEventValue = Cells(Target.Row, 10).Text
        
        Application.CalculateFull
        
        newEventValue = Cells(Target.Row, 10).Text
        
        Sheets("Schedule").Range("F2:F2000").Replace What:=oldEventValue, Replacement:=newEventValue
        
        End If
        
     Application.CalculateFull

ErrorExit:
    
    Exit Sub

ErrorHandler:

    Debug.Print Err.Number & vbNewLine & Err.Description
    Resume ErrorExit

End Sub