Excel依赖下拉列表-多行 [英] Excel Dependent Dropdown List - Multiple Rows

问题描述

我正在尝试为Excel中的多行创建一个依赖的下拉列表.本质上，需要发生的是用户可以为单元格A1选择一个值，并且该值将确定他们可以在单元格B2中选择的值.但是，我需要对多行执行此操作-我遇到的麻烦是，如果我仅将数据验证复制到B列下，那么在所有其他行中，它都引用单元格A1，而不是单元格A(x).

有什么想法吗?

解决方案

您可以结合使用命名范围，间接公式和数据验证.对于A1下拉列表中的所有可能结果，您需要创建一个等于A1的命名范围.然后，要在单元格B2中进行数据验证，请使用公式 = Indirect(a1)

示例:A1 =(披萨或沙拉)

创建两个命名范围:在PIZZA命名的范围中，您可能会遇到诸如奶酪，小辣椒，日本菜，洋葱等..."的内容.
在名为SALAD的范围内，您可能会遇到诸如牧场，胡萝卜，山羊奶酪，...等"之类的东西

输入方程式后，B2中的下拉列表将取决于A1中的值.

此链接详细介绍您的需求.毫无价值的是，只有在A1中的值没有空格(命名范围不允许有空格)时，它才会起作用.您可以通过使用替代功能来解决此问题. A2 =替代(A1，"，")

I am trying to create a dependent drop-down list for multiple rows in excel. Essentially, what needs to happen is that the user can select a value for cell A1 and this value will determine the value that they can select in cell B2. However, I need this to be done for multiple rows - the trouble I run into is that if I just copy the data validation down column B, then in all other rows, it references cell A1, not cell A(x).

Any ideas?

解决方案

You can do this with a combination of named ranges, the indirect formula, and data validation. For every possible outcome in your A1 drop down, you need to create a named range equal to A1. Then, for data validation in your cell B2, use the formula =Indirect(a1)

Example: A1 = (PIZZA or SALAD)

Create two named ranges: In the PIZZA named ranges you may have something like "Cheese, Pep, Jap, Onions, etc,...."
In the SALAD named ranges you may have something like "Ranch, Carrots, Goat Cheese,....etc"

Once you input your equation, your drop down in B2 will depend on what value you have in A1.

This link goes into more detail about what you need. It's worth nothing that this will only work if your values in A1 do not have spaces (named ranges do not allow for spaces). You can work around this by using the substitute function. A2 = Substitute(A1," ","")

这篇关于Excel依赖下拉列表-多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！