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

Timber5

New Member
Joined
May 29, 2024
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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.

001.png


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!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
In the sheet module (e.g. Sheet1), not in the standard module (e.g. Module1), insert the following code:
VBA 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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,155
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top