I have one worksheet for setting up the data sources of multiple data validation lists. in other words, I'm using this worksheet to provide drop down lists to multiple other worksheets.

I need to dynamically update all worksheets upon any of a single or several changes on the data source worksheet. I may understand this should come with event macro over the entire workbook.

My question is how to achieve this keeping the "OFFSET" formula across the whole workbook ?

Thx

To support my question, I put the piece of code that I'm trying to get it working :

Provided the following informations :

• I'm using such a formula for a pseudo dynamic update of the drop down lists, for example :

=OFFSET(MyDataSourceSheet!$O$2;0;0;COUNTA(MyDataSourceSheet!O:O)-1)

• I looked into the pearson book event chapter but I'm too noob for this.
• I understand this macro and implemented it successfully as a test with the drop down list on the same worksheet as the data source. My point is that I don't know how to deploy this over a complete workbook.

Macro related to the datasource worksheet :

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' Macro to update all worksheets with drop down list referenced upon
' this data source worksheet, base on ref names

    Dim cell As Range
    Dim isect As Range
    Dim vOldValue As Variant, vNewValue As Variant

    Dim dvLists(1 To 6) As String 'data validation area
    Dim OneValidationListName As Variant

    dvLists(1) = "mylist1"
    dvLists(2) = "mylist2"
    dvLists(3) = "mylist3"
    dvLists(4) = "mylist4"
    dvLists(5) = "mylist5"
    dvLists(6) = "mylist6"

    On Error GoTo errorHandler

    For Each OneValidationListName In dvLists

        'Set isect = Application.Intersect(Target, ThisWorkbook.Names("STEP").RefersToRange)
        Set isect = Application.Intersect(Target, ThisWorkbook.Names(OneValidationListName).RefersToRange)

        ' If a change occured in the source data sheet
        If Not isect Is Nothing Then

            ' Prevent infinite loops
            Application.EnableEvents = False

            ' Get previous value of this cell
            With Target
                vNewValue = .Value
                Application.Undo
                vOldValue = .Value
                .Value = vNewValue
            End With

            ' LOCAL dropdown lists : For every cell with validation
            For Each cell In Me.UsedRange.SpecialCells(xlCellTypeAllValidation)
                With cell
                    ' If it has list validation AND the validation formula matches AND the value is the old value
                    If .Validation.Type = 3 And .Validation.Formula1 = "=" & OneValidationListName And .Value = vOldValue Then

                        ' Debug
                        ' MsgBox "Address: " & Target.Address

                        ' Change the cell value
                         cell.Value = vNewValue



                    End If
                End With
            Next cell

            ' Call to other worksheets update macros
             Call Sheets(5).UpdateDropDownList(vOldValue, vNewValue)

            ' GoTo NowGetOut
            Application.EnableEvents = True

        End If
     Next OneValidationListName


NowGetOut:
    Application.EnableEvents = True
    Exit Sub

errorHandler:
    MsgBox "Err " & Err.Number & " : " & Err.Description
    Resume NowGetOut


End Sub

Macro UpdateDropDownList related to the destination worksheet :

Sub UpdateDropDownList(Optional vOldValue As Variant, Optional vNewValue As Variant)

        ' Debug
        MsgBox "Received info for update : " & vNewValue

        ' For every cell with validation
        For Each cell In Me.UsedRange.SpecialCells(xlCellTypeAllValidation)
            With cell
                ' If it has list validation AND the validation formula matches AND the value is the old value
                ' If .Validation.Type = 3 And .Value = vOldValue Then
                If .Validation.Type = 3 And .Value = vOldValue Then
                    ' Change the cell value
                    cell.Value = vNewValue
                End If
            End With
        Next cell

End Sub