excel下拉列表联动_动态相关Excel下拉列表

excel下拉列表联动

With dependent data validation, you can make one drop down list depend on the selection in another cell. For example, select Vegetables as a category in column B, and you'll see a drop down list of vegetables in column C.

使用相关数据验证 ，您可以根据另一个单元格中的选择创建一个下拉列表。 例如，在B列中选择蔬菜作为类别，您将在C列中看到蔬菜的下拉列表。

间接问题 (Problems With INDIRECT)

This technique uses the INDIRECT function, to return the range with the name Vegetables. It works well with a static named range, but INDIRECT doesn't work with a dynamic range.

该技术使用INDIRECT函数返回名称为Vegetables的范围。 它适用于静态命名范围，但INDIRECT不适用于动态范围 。

For example, in the workbook shown below, there is a dynamic range named NumList, with the formula:

例如，在下面显示的工作簿中，有一个动态范围名为NumList ，其公式为：

=Admin!$A$1:INDEX(Admin!$A:$A,COUNTA(Admin!$A:$A))

=管理员！$ A $ 1：INDEX(管理员！$ A：$ A，COUNTA(管理员！$ A：$ A))

You can SUM the dynamic named range:

您可以对动态命名范围求和：

=SUM(NumList)

= SUM(NumList)

for the correct result of 10.

以获得正确结果10。

However, if you use the INDIRECT function, the result is a #REF! error. =SUM(INDIRECT(C4))

但是，如果使用INDIRECT函数，则结果为#REF！ 错误。 = SUM(INDIRECT(C4))

相关数据验证解决方法 (Dependent Data Validation Workaround)

Instead of using dynamic ranges with dependent data validation, you can use a named starting cell (Vegetables) and named column (VegetablesCol).

您可以使用命名的起始单元格(Vegetables)和命名的列(VegetablesCol)，而不是使用动态范围进行相关数据验证。

Then, refer to those named ranges in the dependent data validation formula.

然后，请参考相关数据验证公式中的那些命名范围。

=OFFSET(INDIRECT($B2),0,0,COUNTA(INDIRECT(B2&"Col")),1)

= OFFSET(INDIRECT($ B2)，0,0，COUNTA(INDIRECT(B2＆“ Col”))，1)

You can see this example, and a formula that substitutes invalid characters, on the Contextures website, dependent data validation page. ___________

您可以在Contextures网站的相关数据验证页面上看到此示例以及一个替换无效字符的公式。 ___________

翻译自: https://contexturesblog.com/archives/2011/05/20/dynamic-dependent-excel-drop-downs/

excel下拉列表联动