如何基于多个条件创建过滤的下拉列表 [英] How to create a filtered drop down list based on multiple criteria

问题描述

在下面的 Schedule 图像上，我试图在"腺体(A)"中创建下拉列表.柱子.不仅是创建所有可用的"腺体"列表，我希望根据" CORES/PAIRS "，" SIZE mm "中的数据过滤该列表.和"电缆类型".对于此示例，我们将使用"3c 16 BS5467，XLPE/SWA/PVC".

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".

计划

要确定列表的过滤器，请使用" IDØ(mm)"和"外径Ø(mm)"有关需要考虑选择的电缆的信息，请参见下面的电缆图像.如您所见，在示例中，我们使用的电缆具有" ID " 15.5 和" OD "的 20.35 .

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.

电缆

最后在下面的腺体图像中看到" ID "上方的数值必须在"内部最小/最大"范围内和" OD "需要在"外部最小/最大"之内.

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".

腺体

因此，回到"腺体(A)"中的第一张图片下拉列表的第4行的列应进行过滤，并且仅显示串联的值:

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:

• 151/RAC/B/M25
• 501/453/UNIV/B/M25
• ICG/653/UNIV/B/M25

在两个单独的公式中，我设法 VLOOKUP 仅将" OD "根据电缆类型:

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)

然后根据获取的值 LOOKUP "格兰尺寸"来自"外部最小/最大"内部:

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)

问题是我不知道如何包括检查" ID "同样，还可以检索级联的单元格(" GLAND TYPE "和" GLAND SIZE ")，然后使它们成为 Data Validation Drop向下列表.

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.

谢谢

推荐答案

好的，这很难解释.我将尽我所能.也许，如果我们将其包装在专用纸上，我们不会犯错.

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.

阶段1:创建一个新工作表.

创建一个新工作表并将其命名为计算".我们将把大多数东西放在这里.首先，我们输入"Schedule中的选定行"在单元格A1中.

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.

阶段2:确定选择的电缆号.

由于时间表"表中有多个电缆入口，因此我们将需要多个可能的空地清单.为每个车道创建专用列表或限制用户自由度将是不切实际的.因此，我们需要知道用户在计划表"中选择的行.我们必须使用VBA.右键单击计划表"名称标签，然后单击查看代码".将此代码复制粘贴到出现的窗口中:

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

此代码将在工作表表格的单元格A2中报告实际在工作表明细表中选择的行号.每次更改选择时，值都会更改.它还防止在计划"表(将放置Glades下拉列表的列)中选择F列的多行.您可以通过更改计划"表中的选择并在计算"表中查看结果来测试代码.

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.

阶段3:确定选择哪种类型的电缆及其ID/OD.

在计算"表中，键入选定的电缆"，然后单击确定".在范围B1中.在范围B2中，输入以下公式:

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)

此公式将重新构造我们将在电缆"表的查找"列中搜索的名称.这是一系列INDEX函数，没有什么真正复杂的.

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.

现在我们知道要查找的内容，我们可以提取其ID/OD.输入"ID"单元格C1中的"OD"和"OD"位于单元格C1中.在单元格D1中.在单元格C2中，输入以下公式:

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)

在D2单元格中输入以下公式:

In cell D2 type this formula:

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

这些公式将在电缆"表中搜索电缆列表，并提取给定电缆的ID/OD.

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

阶段4:创建过滤列表.

您的腺体列表在第三行中有其第一腺体.因此，为了使交叉检查数据更加容易，我们将相应地放置公式.在工作表计算类型中，输入列表阶段1".在单元格E2中.在单元格E3中，输入以下公式:

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(),"")

将其一直向下拖动，直到其覆盖腺体"表中腺体列表的相同行数.该公式将突出显示"公式.我们要寻找的腺体在哪些行中(如果有的话).此时，列表很长，没有排序，并且可能有很多空白单元格.我们需要对其进行排序.在单元格F2中，键入列表阶段2".在单元格F3中，输入以下公式:

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)),"")

像上一个一样将其向下拖动.现在我们有了一个紧凑的数字列表.我们需要将它们翻译成林间空地的名字.在单元格G2中，键入过滤后的腺体列表".在单元格G3中，输入以下公式:

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.

阶段5:为列表命名.

我们需要为列表创建一个动态引用，以切出所有空白单元格.定义一个新名称，称为Gland_Filtered_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)

阶段6:插入数据验证.

在计划"表中，使用列表模式并将Gland_Filtered_List作为起点，为腺体列创建数据验证.

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.

这篇关于如何基于多个条件创建过滤的下拉列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！