create drop down with dates listed; then excel auto navigate to cell with selected date

Create a unique date list for the drop-down, then use a Worksheet_Change macro to select the first matching date whenever P3 changes.

1. Create the unique date list

Choose an unused helper cell, such as R3, and enter:

=SORT(UNIQUE(P25:P1000))

UNIQUE returns each date once and spills the results down automatically ^1^. Adjust P1000 if your list extends farther.

Format the helper range as Long Date if necessary. Keep the cells below R3 empty so the formula can spill.

2. Create the drop-down in P3

1. Select P3.
2. Select Data > Data Validation.
3. Under Allow, select List.
4. In Source, enter:

      =$R$3#
   

5. Ensure In-cell dropdown is selected, and then select OK.

The # operator references the complete spilled range, so the drop-down expands or contracts with the unique list ^2^. These are the standard steps for creating a data-validation list ^3^.

You can hide column R afterward.

3. Add automatic navigation

Right-click the worksheet tab containing these cells, select View Code, and paste this into that worksheet’s code window:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim searchArea As Range
    Dim foundDate As Range

    If Intersect(Target, Me.Range("P3")) Is Nothing Then Exit Sub
    If Target.CountLarge > 1 Or Not IsDate(Target.Value) Then Exit Sub

    Set searchArea = Me.Range("P25:P1000")
    Set foundDate = searchArea.Find(What:=Target.Value2, _
        After:=searchArea.Cells(searchArea.Cells.Count), _
        LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext)

    If Not foundDate Is Nothing Then Application.Goto foundDate, True
End Sub

The worksheet-change event runs when the user changes P3 ^4^. Range.Find returns the first exact matching cell; its search arguments are specified explicitly because Excel retains previous Find-dialog settings ^5^.

For example, selecting Thursday, September 3, 2026 will select and scroll to P37, assuming that is its first occurrence.

Finally:

1. Select File > Save As.
2. Save as Excel Macro-Enabled Workbook (*.xlsm).
3. Allow macros when opening the workbook.

VBA automation requires the desktop Excel application; it does not run in Excel for the web ^6^.

If no navigation occurs, verify that both P3 and the source cells contain real Excel dates rather than text that only looks like a date.

References

1. UNIQUE function | Microsoft Support
2. Spilled range operator | Microsoft Support
3. Create a drop-down list | Microsoft Support
4. Worksheet.Change event (Excel)
5. Range.Find method (Excel)
6. How do I command one column to update the date based off the update to another column? - Microsoft Q&A