Excel-如何创建使用不同级别的从属下拉列表? [英] Excel - how to create a dependent drop down list that uses different levels?

问题描述

我正在尝试确定如何创建一个动态下拉列表，该列表将考虑两个不同的属性.

I'm trying to determine how to create a dynamic drop-down list that will take into consideration two different attributes.

在下面的示例中，我具有三个成本要素:人工，基础设施和许可证.这些每个都有不同的级别.例如，L1的人工具有:存储管理，数据库管理和计算管理.在L2，它具有:经理，副总裁和常务董事.

In my example below, I have three cost elements: Labor, Infrastructure, and Licenses. Each of these have different levels. For example, Labor at L1 has: Storage Management, Database Managed, and Compute Management. At L2 it has: Manager, Vice President, and Managing Director.

我希望在E18:F20单元格中，每个单元格都有一个下拉列表，其中包含该成本要素和该级别.

I want in cells E18:F20 for each cell to have a drop down list, for that cost element and for that level.

我尝试使用间接"公式，但是您需要具有特定的命名范围.我需要一些可以基于两个属性(成本要素和水平)查找范围的东西.

I've tried using the "Indirect" formula but you need to have the specific named range. I need something that can look up a range based on two attributes, the cost element and level.

有人知道我应该在数据验证"字段中键入什么吗?

Anyone know what I should type in the Data Validation field?

推荐答案

命名范围不能有空格，因此您的范围名称将必须使用下划线或对于相关范围完全省略该字符.例如"LaborL2".

Named ranges cannot have spaces, so your range names will have to use an underscore or omit that character altogether for the dependent ranges. For example "LaborL2".

定义所有这些命名范围，LaborL1，LaborL2，InfrastructureL1等.

Define all these named ranges, LaborL1, LaborL2, InfrastructureL1, etc.

然后，您可以使用Indirect函数连接$ F18和G $ 17.喜欢

Then you can use the Indirect function to concatenate $F18 and G$17. like

=INDIRECT($F18&G$17)

然后，您可以在下拉列表中看到正确的列表.

Then you can see the correct list in the dropdown.

这篇关于Excel-如何创建使用不同级别的从属下拉列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！