可以根据单元格中的1值制作不同的2个下拉列表？ [英] It is possible to make 2 dropdownlist with different based on 1 value in a cell?
问题描述
我的问题是，我想添加第三个依赖单元格，并且问题 MyCell 的名称已经使用一次
我使用这一行代码绑定第一个下拉列表（ INDIRECT（SUBSTITUTE（$ U22，，）&Col）），1）
是否可以添加到这行代码如
（INDIRECT（SUBSTITUTE（$ U22 +Other，，）& Col）），1）`
为了绑定第二个下拉菜单，所以我可以命名一个单元格： MyCellOther 女巫包含我要添加的其他值
我会详细说明一下这意味着在我上面的评论中：
命名范围的名称可以是任何您尚未被Excel用作术语的内容。当命名你的范围时，你应该小心使用直观地显示他们的意思的逻辑术语。如果您在有序列表中使用一系列相似的名称，请考虑将它们简单地编号为1到9，或者考虑将2D数据块用作单个范围。
多层下拉列表的说明示例 - 2种方法
Sheet1将保留最终用户的下拉列表选择。 Sheet2将保存您的原始列表数据库。
在Sheet1上，假设A1是一个单元格，允许用户输入他们想要的饼干类型。有两种类型：水果或肉类。此外，每种类型有两个子类别：水果（夏季）/水果（冬季）或肉类（红肉）/肉类（家禽）。
如果用户在A1中输入了所需的类型（可能是一个下拉菜单），我们希望A2显示一个下拉列表，允许他们进一步选择哪个他们想要的子类别。然后，我们希望A3根据主要类型中的该子类别显示每个特定饼图的下拉列表。
在A1中创建下拉列表
在Sheet2上，A1输入Fruit，然后在A2上输入Meat。在sheet2上选择A1：A2，名称范围为MAIN_TYPES。在Sheet1，A1上，创建一个限制为= MAIN_TYPES的下拉列表。这将创建第一个下拉列表。
使用2D数据块创建A2中的子类别下拉列表
首先，在sheet2上组织数据。在Sheet2 B1上，键入Summer，在C1类型Winter上。在B2型B2型红肉，C2型家禽。选择B1：C2，并将范围命名为SUB_CATEGORIES。
所以，用户已经选择了水果或肉类。我们希望此下拉列表仅显示该特定类型的子类别。我们可以通过引用Sheet2行1包含果馅饼的事实来做到这一点，第2行包含肉馅饼。
在Sheet1 A2上，使单元格成为下拉列表它指的是SUB_CATEGORIES列表。但是，我们想限制它，以便它不显示其他饼类型的类别。如果我们想要硬编码，只显示水果子类别，我们可以使用：
= INDEX（SUB_CATEGORIES，1， 0）
这表示 - 取第1行SUB_CATEGORIES（水果行）采取所有列（我们通过将'0'显示在上面），它假设我们想要所有可用的列，这是非常方便的）。
为了使这个动态和基于A1的选择，我们可以用MATCH公式替换上面的'1'，它查看在数据行/列中找到特定搜索字符串的位置。如下所示：
= INDEX（SUB_CATEGORIES，MATCH（A1，MAIN_TYPES，0）
这将搜索在A1中从MAIN_TYPES范围中挑选的派生类型，然后将该行号返回给INDEX函数，现在，基于与所选MAIN_Type对齐的行，该INDEX函数返回一个子行类型（和两列）。
创建下拉列表在A3中列出，使用编号的命名范围
在sheet2上添加最终的数据块 - 每种类型的饼图的具体列表。在D1类型桃子，D2型樱桃，D3型大黄;名称此列表Fruit1 [这将是夏季水果]。E1型苹果，E2型南瓜;名称此列表Fruit2 [这将是冬季水果]。在F1类型牛肉，F2型羊肉;将此列表命名为Meat1 [这将是红肉]。在G1型鸡身上，将此细胞命名为Meat2 [这将是家禽]。
现在，在Sheet1上，单元格A3，th e用户将需要选择已经选择的类型和子类别。如果我们想要将其硬编码为冬季水果，下拉列表的公式将只是：
= Fruit2
但是，如果选择了上述，我们只想显示冬季水果选项。考虑一下：如果我们想允许用户在A4中输入Meat1，我们可以在A3中输入以下公式，并显示该列表：
= INDIRECT（A4）
而不是让用户输入子类别的名称，我们将获取他们已经提供的信息，并将其组合以创建该名称。具体来说，我们将结合（1）用户已经选择的主要饼图，（2）他们选择的子类别的列号。他们选择的主要类型是A1中的文本。子类别的列号可以使用Index&匹配函数，类似于上述：
= MATCH（A2，INDEX（SUB_CATEGORIES，MATCH（A1，MAIN_TYPES，0），0 ）
记住上面的公式'= INDEX（SUB_CATEGORIES，MATCH（A1，MAIN_TYPES，0）给我们一个与水果或肉一致的行，在这里我们采取这一行，并使用MATCH找到与A2对齐的列＃，我们可以将所有这些与A1中的文本结合在一起，它将给我们A3中最终下拉列表的子类别的名称：
= INDIRECT（A1& MATCH（A2，INDEX（SUB_CATEGORIES ，MATCH（A1，MAIN_TYPES，0），0））
这就是它的一切。
以上内容可以为您的数据进行推断，但显示如何（1）执行引用名为MAIN_TYPES的列表的下拉列表;（2）在SUB_CATEGORIES的2D范围内查找特定行的下拉列表;（3）使用间接函数创建级联下拉列表;（4）使用n以范围名称引用多个相似列表。
After reading many tutorial, I managed to make a well effective dynamic dropdownlist
My question is , let's say I want to add a third depend cell, and the problem the name of MyCell is already used once
I'm using this line of code to bind the first dropdownlist (INDIRECT(SUBSTITUTE($U22," ","")&"Col")),1)
Is it possible to add to this line of code something like
(INDIRECT(SUBSTITUTE($U22+"Other"," ","")&"Col")),1)`
as to bind the second dropdownlist so I can name a cell :MyCellOther witch contains the other value I want to add
I'll elaborate on what I meant in my comment above:
The name of a Named Range can be anything you want that isn't already used as a term by Excel. When naming your ranges, you should be careful to use logical terms which intuitively show what they mean. If you are using a series of similar names in an ordered list, consider simply numbering them 1 to 9, or instead consider using a 2D block of data as a single range. I will show you how to do both.
Explanatory Example of multi-tiered dropdown lists - 2 methods
Sheet1 is going to hold the end-user's dropdown selection. Sheet2 is going to hold your raw list database.
On Sheet1, assume A1 is a cell that allows a user to enter in what type of pie they want. There are 2 types: Fruit or Meat. Further, there are two sub-categories within each type: Fruit (Summer) / Fruit (Winter), or Meat (Red Meat) / Meat (Poultry).
If a user enters the type of pie they want in A1 (could be a dropdown itself), we want A2 to show a dropdown list that allows them to further choose which sub-category they want. Then, we want A3 to show a dropdown of each specific pie based on that sub-category within the main type.
Creating the Dropdown list in A1
On Sheet2, A1 enter "Fruit", and then on A2 enter "Meat". Select A1:A2 on sheet2, and name that range "MAIN_TYPES". On Sheet1, A1, make a dropdown list that is limited to "=MAIN_TYPES". This will create the first dropdown list.
Creating the sub-category dropdown list in A2 using a 2D block of data
First, organize your data on sheet2. On Sheet2 B1, type Summer, on C1 type Winter. On Sheet2 B2 type Red Meat, on C2 type Poultry. Select B1:C2, and name that range "SUB_CATEGORIES".
So, the user has already selected either Fruit or Meat. We want this dropdown list to only show the sub-category of that particular type. We can do this by referencing the fact that Sheet2 row 1 contains the fruit pies, and row 2 contains the meat pies.
On Sheet1 A2, make the cell a dropdown list which refers to the list SUB_CATEGORIES. But, we want to limit it so that it doesn't show the categories for the 'other' pie type. If we wanted to hardcode this to only show the Fruit sub-categories, we could use:
=INDEX(SUB_CATEGORIES,1,0)
This says - take row 1 of SUB_CATEGORIES (the fruit row), and for the column, take ALL columns (we show this by putting the '0' above; it assumes we want all the available columns, which is quite handy).
To make this dynamic and based on the selection of A1, we can replace the '1' above with the MATCH formula, which looks at where a specific search string was found in a row/column of data. Like so:
=INDEX(SUB_CATEGORIES,MATCH(A1,MAIN_TYPES,0)
This searches for the type of pie picked in A1, out of the MAIN_TYPES range, and then returns that row number to the INDEX Function. So now, this INDEX function returns a single row (and both columns) of the sub-categories, based on the row which aligns with the MAIN_Type chosen.
Creating the dropdown list in A3, using numbered Named Ranges
On sheet2, add in the final block of data - the specific list of each type of pie. On D1 type Peach, on D2 type Cherry, on D3 type Rhubarb; Name this list Fruit1 [this will be the summer fruits]. On E1 type Apple, on E2 type Pumpkin; Name this list Fruit2 [this will be the winter fruits]. On F1 type Beef, on F2 type Lamb; Name this list Meat1 [this will be the Red meats]. On G1 type Chicken; Name this cell Meat2 [this will be the Poultry].
Now, on Sheet1, cell A3, the user will need to pick which pie out of the type and sub-category already chosen. If we wanted to hardcode this to be winter fruits, the formula for the dropdown list would simply be:
=Fruit2
But, we want to only show the winter fruit options if that was chosen above. Consider: if we wanted to allow the user to just type "Meat1" into A4, we could put the following formula in A3, and it would show that list:
=INDIRECT(A4)
Instead of having the user type in the name of the Sub Category, we will take the information they have already provided, and combine it to create that name. Specifically, we will combine (1) the main of pie the user has already chosen, with (2) the Column number of the Sub_Category they have chosen. The Main type they chose is simply the text in A1. The column number of the Sub Category is found using the Index & Match function, similar to above:
=MATCH(A2, INDEX(SUB_CATEGORIES,MATCH(A1,MAIN_TYPES,0),0)
Remember above, the formula '=INDEX(SUB_CATEGORIES,MATCH(A1,MAIN_TYPES,0)' gave us the row that aligned with either Fruit or Meat. Here we take that row, and use MATCH to find the column # that aligns with A2. We can combine all of this together with the text in A1, and it will give us the name of the Sub Category for the final dropdown list in A3:
=INDIRECT(A1 & MATCH(A2, INDEX(SUB_CATEGORIES,MATCH(A1,MAIN_TYPES,0),0))
And that's all there is to it.
The above can be extrapolated for your data, but it shows how to (1) do a dropdown list that refers to a named list of MAIN_TYPES; (2) Do a dropdown list that looks for a specific row in a 2D range of SUB_CATEGORIES; (3) create a 'cascading' dropdown list using the Indirect function; and (4) use numbers in the name of a range to refer to multiple similar lists.
这篇关于可以根据单元格中的1值制作不同的2个下拉列表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
