Excel中的多个相关的下拉列表 [英] Multiple Dependent Dropdown Lists in Excel
问题描述
所以我一直在处理这个问题一段时间，至今没有提出任何有效的解决方案。我正在尝试创建三个下拉列表。一个用于 Dristrict ，阻止和村。
So I've been working on this problem for a while and haven't come up with any efficient solution so far. I am trying to create three dropdown lists. One for "Dristrict", "Block" and "Village".
这些类别是如何相互关联的：
This is how the categories are related to each other:
- 有很多地区。
- 每个区域内有多个区块。
- 每个街区内有多个村庄。
这是我的数据当前的结构，我可以接受不同结构的建议。目前，每个区，街区和村庄都有自己的合并（或单个）单元格，您可以看到。所有村庄都会占据一个单元格。
This is how my data is currently structured, I am open to suggestions for a different strutcure as well. Currently each district, block and village has its own merged (or single) cell as you can see. All villages will take up exactly one cell.
我已经考虑过的另一个结构如下。也许它会帮助，也许它不会？如果我要使用VBA宏，那么这将帮助我，但是在下拉列表中似乎不起作用。
Another structure that I had considered is below. Maybe it'll help, maybe it wont? It would have helped me if I were to use a VBA macro however those don't seem to work in dropdown lists.
所以这是我的思维过程，我无法转换成excel公式代码，在下拉列表数据验证中工作。
So this is my thought process that I am unable to convert into excel formula code that works in the dropdown list data validation.
- 获取所有区域的列表。 （完成！） - 我只需创建一个单独的列表，并创建一个下拉列表。
- 获取所有单元格的范围（由于下拉列表需要逗号分隔列表或范围）当前单元格左侧的单元格与区域下拉列表中选择的单元格相同。
- 对于村名和块名重复步骤2。
我相信我可以创建区域+块命名列表的数量，使用 INDIRECT（）函数。但我不知道这是否是最好的方式。
I believe that I can create districts + blocks number of named lists to achieve this with use of the INDIRECT() function. But I am not sure if that is the best way.
欢迎所有解决方案！应在Excel 2003及以上版本中工作。谢谢！
All solutions welcome! Should work in Excel 2003 and above. Thanks!
更新： 我想要下拉列表（区，块，村）每行它是一个大数据条目excel表格。
Update: I want the dropdown lists (District, Block, Village) one in each row Its a big data entry excel sheet I'm making.
谢谢！
编辑：它的区域+块不是区域*块（感谢上帝）
Its districts+blocks not districts*blocks (Thank god)
推荐答案
鉴于问题在中途改变，这是另一个答案，建立在我的第一个。
Given that the question changed midway, here's another answer, building on my first one.
这个文件是一个href =https://mega.co.nz/#!LBQjRSCJ!aeRdwWLktntE-QVUcSBYPjHQOaQ_CCQlCinQRHnSfys =nofollow。它基于上述解决方案，但具有一个迷你宏，以CurrentRow的名义提供当前的行号。我使用这个名字来检索当前行的区块/块，并根据这一点构建下拉列表。
Check out this file. It is based upon the above solution, but has a mini macro that provides the current row number in the name "CurrentRow". I use this name to retrieve the district/block of the current row - and construct the drop downs based on this.
我几乎可以通过提供宏来工作公式 = ROW（INDIRECT（CELL（address）））到CurrentRow - 但是您需要手动按 F9 （更新计算），然后点击下拉菜单...
I almost got it to work without a macro by providing the formula =ROW(INDIRECT(CELL("address"))) to the name "CurrentRow" - but then you would need to manually press F9 (to update the calculation) before your click on the drop down...
这篇关于Excel中的多个相关的下拉列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
