开发者

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   开发者_JAVA百科   Item          Price
2 | Dairy         Milk          $ 2.00
3 |               Bread         $ 3.00
4 | Fruit         Apple         $ 0.60
5 |               Bannana       $ 0.75

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.

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.


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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜