Automated drop down based on 1st selection + other column data

[Timber5]

So basically there are 2 columns:
- A. contains a job name info. Updated daily, different names, different naming conventions, e.g. 'HO#0001' through 'HO#50000', 'job#51', etc. (there is no set list/data range to work with). However, there are always multiple entries for a particular job (based on supplier info in another column).
- B. contains a 'Yes' / "No" drop down list for whether or not a particular job is going to be audited. If decision is made to audit HO#0001, it will be audited for all entries/suppliers, no exceptions.

The question: is it possible to automatically populate the rest of rows in column B for a particular job title (e.g. HO#0001), once the first selection (Yes/No) is made? because if it is Yes, it's going to be Yes for all HO#0001 entries and the idea is to avoid manual logging at all costs to avoid errors or omissions.

Thanks!

 

[Artik]

In the sheet module (e.g. Sheet1), not in the standard module (e.g. Module1), insert the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LO As ListObject
    Dim vIDs As Variant
    Dim i As Long
    Dim lRow As Long

    If Target.CountLarge > 1 Then Exit Sub

    Set LO = Target.ListObject

    If Not LO Is Nothing Then
        If Not Intersect(Target, LO.ListColumns(2).DataBodyRange) Is Nothing Then
            vIDs = LO.ListColumns(1).DataBodyRange.Value
            lRow = Target.Row - LO.Range.Row

            Application.EnableEvents = False

            For i = 1 To UBound(vIDs)
                If vIDs(i, 1) = vIDs(lRow, 1) Then
                    LO.ListRows(i).Range.Cells(, 2).Value = Target.Value
                End If
            Next i

            Application.EnableEvents = True
        End If
    End If

End Sub

Artik

 

[Timber5]

Am I doing it right? No effect so far:

 

[Timber5]

Actually, I think the problem is that I indicated the wrong columns in my initial post. The first one is not A, but G and the Yes/No column is not B, but L.

 

[Artik]

The code is based on references to columns/rows of the table, not the sheet. This is especially important when the table does not start in A1. So, don't look at the sheet column headers, but at the table headers. So the reference LO.ListColumns(2) is the second column of the table and similarly LO.ListColumns(1) is the first column. So correct for the correct numbering in these references. One more change will be to the reference LO.ListRows(i).Range.Cells(, 2).Value . In the Cells(, 2) fragment, change the column number from 2 to the column number of the table named Audit.

I hope this code is supposed to work when you make changes to the sheet named June_2024. If not, move the code to the correct module.

Artik

 

[Timber5]

The code is based on references to columns/rows of the table, not the sheet. This is especially important when the table does not start in A1. So, don't look at the sheet column headers, but at the table headers. So the reference LO.ListColumns(2) is the second column of the table and similarly LO.ListColumns(1) is the first column. So correct for the correct numbering in these references. One more change will be to the reference LO.ListRows(i).Range.Cells(, 2).Value . In the Cells(, 2) fragment, change the column number from 2 to the column number of the table named Audit.

I hope this code is supposed to work when you make changes to the sheet named June_2024. If not, move the code to the correct module.

Artik

Amazing, many thanks!
I've changed column numbers accordingly (1 > 7 and 2 > 12) and everything works just fine now.
Thanks a lot, much appreciated.