Data validation list values on excel worksheet for Mac OS not updating automatically

Question

In an Excel worksheet for expense accounting, I have cells and columns with lookup lists that are located on another sheet within the same file. I have set this up using the "Data Validation - Lists" command, for example as in the following screenshot:

When I change the cell values of these lookup lists, they are not updating automatically in the other worksheets. I have already searched for help articles and have tried the following:

• Mac OS has no trust center - cannot change settings as for Windows users
• Calculation as "use iterative calculation" checked
• Calculation options are set to "automatic"
• When I am in the Data tool panel, "Queries & Connections" is grayed out and has no options

If the lookup lists are not updating automatically, of course it defeats the purpose to have them! Please help. Thank you.

Answer 1

Hi @Berrigan

Thank you for reaching out to the Microsoft Q&A Forum. 

I understand it can be confusing when changes to your source list don’t automatically appear in the cells that rely on it, especially if you expect the dropdowns to stay in sync across sheets. 

Based on your description, I tested the same setup in Excel for Mac. When I changed a value in the source list (for example, from “A” to “A1”), the dropdown reflected the update the next time I opened it, but the cells that had already selected “A” stayed the same and didn’t update automatically. 

This might be expected behavior as Data Validation only provides a list to choose from and doesn’t create a live link to the source, so selected values remain as plain text. 

If you want existing selections to update automatically when the source list changes, you might consider using a VBA macro to replace those values.  

You can follow the steps and refer to the sample code below, then adjust it as needed for your own environment. 

1.Go to Tools > Macro > Visual Basic Editor. 

2.In the left pane, double-click your source sheet (for example, DE Ausgaben) and paste the code. 

3.Save the file as .xlsm and enable macros when prompted. 

4.Return to Excel and edit a value in your source list to test if it updates automatically. 

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet, c As Range
    Dim oldValue As String, newValue As String
    If Not Intersect(Target, Me.Range("C3:C35")) Is Nothing Then
        Application.EnableEvents = False
        newValue = Target.Value
        Application.Undo
        oldValue = Target.Value
        Target.Value = newValue
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> Me.Name Then
                For Each c In ws.UsedRange
                    If c.Value = oldValue Then c.Value = newValue
                Next c
            End If
        Next ws
        Application.EnableEvents = True
    End If
End Sub

I hope this helps. Please let me know if I’ve misunderstood your question or if you need any further assistance. 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".     

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.