How to autoupdate a list when source data changes

[Susan_Boland]

Hi,

I would really appreciate if someone could help me with this problem:

I Have two lists on my worksheet:

List 1 is a selection for a calculation basis, the entries are "Manual" and "Automatic"

List 2 is dependent on the entries in List 1, as follows: =IF($A$1="Automatic",D2,Vendors), - - This syntax is entered in the List Source field of the data validation tab

Where:
A1 is the Output Parameter from List 1
D2 is the value to show is I want an automatic calculation
Vendors is the named range list to show if List 1 is set to "Manual"

The list syntax works for both lists, when I select Automatic, the correct value populated, when I select Manual, the correct range appears in the List field, however, the output parameter fro List 2 does not automatically update unless I select it and bring the range up.

Can someone help me figure out how to make the list auto update with the correct values for both Manual and Automatic?

[JBeaucaire]

You would need a worksheet_change event watching for changes to cell A1, and based on when that cell changes, force a change into the cell where you List 2 resides. You didn't mention where that cell is, so I will use M1 as an example:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1")) Is Nothing Then
        Application.EnableEvents = False
        Select Case Range("A1").Value
            Case "Manual"
                Range("M1") = "Make Selection..."
            Case "Automatic"
                Range("M1").Value = Range("D2").Value
        End Select
        Application.EnableEvents = True
    End If

End Sub

Change those M1 references to the correct cells for List2.

[Susan_Boland]

Thanks for the help!

Unfortunately this code isn't firing and doesn't automatically update the fields in List 2, Any other thoughts?

Thanks again in advance!