Excel:来自单独工作表的具有动态范围的数据验证 [英] Excel: Data validations with dynamic range from separate worksheet

问题描述

我正在尝试创建一个数据验证列表，其来源来自另一个工作表上的动态范围.例如，采用以下布局

I am trying to create a data validation list whose source comes from a dynamic range on another worksheet. For instance, take the following layout

0 | A             B             C
--|-----------------------------------
1 | Category      Item          Price
2 | Dairy         Milk          $ 2.00
3 |               Bread         $ 3.00
4 | Fruit         Apple         $ 0.60
5 |               Bannana       $ 0.75

当我从另一张纸上的第一个下拉列表中选择 Fruit 时，我希望能够选择 Apple 或 Bannana 从第二.此外，如果我以后添加带有 Carrot 和 Squash 的 Vegetable 类别，以及向 Fruit 添加的另一个项，我希望这些项目能够动态地成为我的数据验证选择的一部分.

When I select Fruit from the first drop down list on another sheet, I would like to be able to select either Apple or Bannana from the second. Furthermore, if I later add a Vegetable category with Carrot and Squash, as well as another item to Fruit, I would like those items to dynamically become part of my data validation selections.

没有VBA，这可能吗?我想保持此单元格公式"为基础，因此不必编写 Application.Volatile 函数.

Is this possible without VBA? I would like to keep this "cell formula" based so I don't have to write a Application.Volatile function.

推荐答案

可以.您必须使用定义的名称作为验证范围的来源，使用众所周知OFFSET()技巧.

Yes you can. You have to use a defined name as the source of the validation range, using the well know OFFSET() trick.

这篇关于Excel:来自单独工作表的具有动态范围的数据验证的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！