[Excel] Populate list via a bi-Condition

Question

Hello excel-world,

im interested to implement something but im not sure if it would be possible and would like your intake on it.

here is my scenario:

i will have two validation cells which will be shown as List that I will pick from. those are my condition that i would like to meet and ommit my list from a database.

I have a list of agents going threw B13:B23 and next two them i have columns of data assuming that my data base looks like this

 B     C      D     E
       X  |   Y  |  Z
agent1 1  |   1  |  0
agent2 0  |   1  |  0
agent3 0  |   1  |  1
agent4 1  |   0  |  0

...

i want to populate a list of agent name from column B, when i select from the validation cell1: X and validation cell2: 1. it should show only

column:
agent1
agent4

or agents in column X with 0...

i read somewhere about array formula but i dont know if this is convenient and i unfortunately dont have any background in macros:( but i know in C++ something like this is fairly easy with conditional statements.

thanks in advance,

Answer 1

It is possible. One way to approach this is to call a sub that lists you the relevant agents whenever the Target in your Worksheet_Change intersects with validation cell1 or cell2 -> the validation changed.

Then you would run a sub with 3 paramenters srcRange, validationColumn and validationValue that goes throgh each row of srcRange and checks if the cell on position rownumber, validationColumn is equal to validationValue if so it outputs the agent and sets outputrow + 1

Put this VBA in your Sheet:

Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim watchRange As Range
        Dim validationValue As Range
        Dim validationColumn As Integer
        Set watchRange = Me.Range("H1, I1") ' Validation Cells '

        If Not Intersect(Target, watchRange) Is Nothing Then
            Set validationValue = Me.Range("I1")
            validationColumn = 0
            With Me.Range("H1")
                If (.value = "X") Then validationColumn = 2
                If (.value = "Y") Then validationColumn = 3
                If (.value = "Z") Then validationColumn = 4
            End With
                listAgents Me.Range("B3:E6"), validationColumn, validationValue
        End If

    End Sub

    Private Sub listAgents(ByRef srcRange As Range, ByVal validationColumn As Integer, ByRef validationValue As Range)

        Dim outputStart As Range
        Dim row As Range
        Dim i As Long

        Set outputStart = Me.Range("H3")
        outputStart.CurrentRegion.Clear

        If validationColumn = 0 Then
            MsgBox "Can't find Validation Column"
            Exit Sub
        End If

        i = 0
        For Each row In srcRange.Rows
            If (row.Cells(1, validationColumn) = validationValue) Then
                outputStart(1 + i, 1) = row.Cells(1, 1)
                i = i + 1
            End If
        Next row
    End Sub

I tested it on your example and it worked.