Need FULL drop down list using INDEX, INDIRECT and MATCH, not just first match

[mAdMaCCa]

Hi

Although i can find threads similar to my situation, I can't find one that gives me enough to solve my issue on my own and so could do with someones help please...

Simplest way to explain is giving you the basic file example which is here on dropbox

in essence, there are three drop down lists. The first is a simple list, "style".
The second is a "category" and the Third needs to be a list based on the chosen "category" AND the chosen "style" from the first drop down list.

I've managed to get it working using INDEX, INDIRECT and MATCH (ignore the SUBSTITUTE, that just strips spaces) but it is only returning the first matching cell, not the whole list...and this is my problem; I need the whole list.

Any help would be amazing, thank you in advance!

 

[Kamolga]

Edit: for some reasons it replaced the word "K.itchen" without point by stars, easiest is to use the linked workbook below for the code


Hi,

It is much easier with combobox . I used

Private Sub Worksheet_Activate()
'Clear list of House Style
 CombHouseStyle.Clear
'Items to add to the list are in named Range "HouseStyle" in *******Units Sheet
Dim rngItems As Range
Dim oDictionary As Object
Set rngItems = Sheet4.Range("HouseStyle")
Set oDictionary = CreateObject("Scripting.Dictionary")
    With CombHouseStyle
        For Each cel In rngItems
            'Avoid duplicates and blanks
            If (oDictionary.exists(cel.Value) Or cel.Value = "") Then
                'Do Nothing
            Else
                oDictionary.Add cel.Value, 0
                .AddItem cel.Value
            End If
        Next cel
    End With
    oDictionary.RemoveAll
 'Clear list of ******* Range
 Comb*******.Clear
 'Items to add to the list are in named Range "Range" in *******Units Sheet
 Set rngItems = Sheet4.Range("Range")
 Set oDictionary = CreateObject("Scripting.Dictionary")
    With Comb*******
        For Each cel In rngItems
            'Avoid duplicates or blanks
            If (oDictionary.exists(cel.Value) Or cel.Value = "") Then
                'Do Nothing
            Else
                oDictionary.Add cel.Value, 0
                .AddItem cel.Value
            End If
        Next cel
    End With
    oDictionary.RemoveAll
End Sub
Private Sub CombHouseStyle_Change()
'Clear list of Colour
 CombColour.Clear
 If Comb*******.Value = "" Then Exit Sub
'Items to add to the list are in table *******Units Sheet
Dim rngItems As Range
Dim oDictionary As Object
Dim Tbl As ListObject
'Define Column of Coulour
Dim i As Long
    For Each cel In Sheet4.ListObjects("TblKtchenUnits").HeaderRowRange
        If cel.Value = Comb*******.Value Then
            i = cel.Column
        End If
    Next cel
'Define Column of "HouseStyle"
Dim j As Long
j = Sheet4.Range("HouseStyle").Column
Set rngItems = Sheet4.ListObjects("TblKtchenUnits").ListColumns(i).DataBodyRange
Set oDictionary = CreateObject("Scripting.Dictionary")
    With CombColour
        For Each cel In rngItems
                If Sheet4.Cells(cel.Row, j) = CombHouseStyle.Value Then
                    'Avoid duplicates and blanks
                    If (oDictionary.exists(cel.Value) Or cel.Value = "") Then
                        'Do Nothing
                    Else
                        oDictionary.Add cel.Value, 0
                        .AddItem cel.Value
                    End If
                End If
        Next cel
    End With
    oDictionary.RemoveAll
End Sub
Private Sub Comb*******_Change()
'Clear list of Colour
 CombColour.Clear
 If CombHouseStyle.Value = "" Then Exit Sub
'Items to add to the list are in table *******Units Sheet
Dim rngItems As Range
Dim oDictionary As Object
Dim Tbl As ListObject
'Define Column of Coulour
Dim i As Long
    For Each cel In Sheet4.ListObjects("TblKtchenUnits").HeaderRowRange
        If cel.Value = Comb*******.Value Then
            i = cel.Column
        End If
    Next cel
'Define Column of "HouseStyle"
Dim j As Long
j = Sheet4.Range("HouseStyle").Column
Set rngItems = Sheet4.ListObjects("TblKtchenUnits").ListColumns(i).DataBodyRange
Set oDictionary = CreateObject("Scripting.Dictionary")
    With CombColour
        For Each cel In rngItems
                If Sheet4.Cells(cel.Row, j) = CombHouseStyle.Value Then
                    'Avoid duplicates and blanks
                    If (oDictionary.exists(cel.Value) Or cel.Value = "") Then
                        'Do Nothing
                    Else
                        oDictionary.Add cel.Value, 0
                        .AddItem cel.Value
                    End If
                End If
        Next cel
    End With
    oDictionary.RemoveAll
End Sub

to manage them. You can access the workbook here:https://1drv.ms/x/s!AvmGsNl7aaaAgtRfpVN2jbR1iN_Pkw . You can download it with the 3 dots menu on top right

and view the code by write clicking the sheet "choice" and click view code. I used your named Range "Range" and "HouseStyle". I created a ifrst combobox "CombHouseStyle", a second "Comb*******" and a last "CombColour".

To handle the output if you are not comfortable with comboboxes, I linked them to A3, B3 and C3.

Note that you can add column and rows to the table without problem but first column of table has to be column A and if you add a name in the "Range", you have to add a column with the same name as you did until now (it can be anywhere in the table)