CODEWITHSUNDEEP
excelexcel-formula
squatman
squatman

Reputation: 71

How to create a filtered drop down list based on multiple criteria

on the below Schedule image I am trying to create a Drop Down List in the "Gland (A)" Column. Rather than just creating a list of all available "Glands" I want that list to be filtered based on the data within "CORES / PAIRS", "SIZE mm" and "CABLE TYPE". For this example we will use a "3c 16 BS5467, XLPE/SWA/PVC".

Schedule

Schedule

To determine the filter for the list, the "ID Ø (mm)" and "OD Ø (mm)" for the select cable need to be taken in to consideration, see Cables image below. As you can see for the example we are using the cable has an "ID" of 15.5 and "OD" of 20.35.

Cables

Cables

Finally seen below in the Glands image, the "ID" from above needs to be within the "INNER MIN/MAX" and the "OD" needs to be within the "OUTER MIN/MAX".

Glands

Glands

So back to the first image in the "GLAND (A)" columns for row 4 the drop down list should be filtered and only show concatenated values:

In two separate formulas I managed to VLOOKUP just the "OD" based on the cable types:

=VLOOKUP(B4&C4&E4,'Cables'!A$2:H$169,8,FALSE)

Then based on the retrieved value LOOKUP the "GLAND SIZE" from within the "OUTER MIN/MAX":

=LOOKUP(2,1/((F4>='Glands'!E$3:E$9 + 1)*(F4<='Glands'!F$3:F$9 - 1)),'Glands'!B$3:B$9)

The problem is I don't know how to include checking the "ID" as well, also to retrieve concatenated cells ("GLAND TYPE" and "GLAND SIZE") and then for them to be a Data Validation Drop Down List.

Any help with this would be greatly appreciated.

Thank you

Upvotes: 2

Views: 1490

Answers (1)

Evil Blue Monkey
Evil Blue Monkey

Reputation: 2609

Ok, this is going to be hard to explain. I'll do my best. Maybe if we wrap this up in a dedicated sheet we won't make mistakes.

PHASE 1: create a new sheet.

Create a new sheet and name it "Calculations". We will put most of the stuff here. First of all we type "Selected row in Schedule" in the cell A1.

PHASE 2: determine what cable number is selected.

Since we have multiple entry of cable in the Schedule sheet, we will need multiple list of possible glades. Creating a dedicated list for each lane or costraining the user freedom would be unpractical. Therefore we need to know what row the user is selecting in the Schedule sheet. We have to use VBA. Right-click on the Schedule sheet name tag and click on "View code". Copy-paste this code in the window that has appeared:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    'Filling the cell A2 in the sheet Calculation with the row number of the selected _
    cell in the scheet Schedule.
    Sheets("Calculations").Range("A2").Value = ActiveCell.Row
    
    'Preventing multiple selection in the F column of the sheet Schedule.
    If Not Application.Intersect(Target, Range("F:F")) Is Nothing Then
        Target.Resize(1, 1).Select
    End If
    
End Sub

This code will report in the cell A2 of the sheet Calculation the row number actually selected in the sheet Schedule. Everytime the selection is changed, the value changes. It also prevent the selection of multiple rows of the F column in Schedule sheet (the column where Glades dropdown list will be placed). You can test the code by changing the selection in the Schedule sheet and looking at the result in Calculations sheet.

PHASE 3: determine what type of cable is selected and its ID/OD.

In the Calculation sheet, type "Selected cable" in range B1. In range B2 type this formula:

=INDEX(Schedule!$A:$F,Calculations!$A$2,2)&INDEX(Schedule!$A:$F,Calculations!$A$2,3)&INDEX(Schedule!$A:$F,Calculations!$A$2,5)

This formula reconstruct the name we will search in the LOOKUP column of the Cables sheet. It's a series of INDEX functions, nothing really complicated.

Now that we know what to look for, we can extract its ID/OD. Type "ID" in the cell C1 and "OD" in cell D1. In cell C2 type this formula:

=VLOOKUP($B$2,Cables!$A:$H,7,FALSE)

In cell D2 type this formula:

=VLOOKUP($B$2,Cables!$A:$H,8,FALSE)

These formulas will search the cables' list in the Cables sheet and extract the ID/OD of the given one.

PHASE 4: create the filtered list.

Your glands' list has its first gland in the third row. So just to make it easier to crosscheck the data, we will place our formulas accordingly. In sheet Calculations type "List stage 1" in cell E2. In cell E3 type this formula:

=IF(AND(C$2>=Glands!C3,C$2<=Glands!D3,D$2>=Glands!E3,D$2<=Glands!F3),ROW(),"")

Drag it all the way down until it will be cover the same number of rows of the glands' list in the Gland sheet. This formula will "highlight" in what rows are the glands we are looking for (if there are any). At this point the list is very long, unsorted and presumably has a lot of blank cells. We need to sort it. In cell F2 type "List stage 2". In cell F3 type this formula:

=IF.ERROR(SMALL(E:E,ROW()-ROW(F$2)),"")

Drag this one down just like the previous one. Now we have a compact list of numbers. We need to translate them into glade's names. In cell G2 type "Filtered gland list". In cell G3 type this formula:

=IF.ERRORE(INDEX(Glands!A:B,F3,1)&"/"&INDEX(Glands!A:B,F3,2),"")

Drag it down again like previously did. We have our list.

PHASE 5: name the list.

We need to create a dynamic reference to the list to cut out all the blank cells. Define a new name calling it Gland_Filtered_List referred to this formula:

=INDIRECT("Calculations!$G$3:G" & ROWS(Calculations!$G$3:$G$1048576) -COUNT.BLANK(Calculations!$G$3:$G$1048576)+2)

PHASE 6: insert data validation.

In the Schedule sheet, create a data validation for the glands column using the list mode and Gland_Filtered_List as origin.

That should do the trick. Right now i have to hurry for work, so i can't check the explanation. Everything should be in order. Try this and ask any question. I'll answer later.

Upvotes: 1

Related Questions