Excel 帮助:动态范围 + 数据验证 [英] Excel Help: Dynamic range + Data Validation
问题描述
我遇到了一些流行的 Excel 问题、动态范围和数据验证下拉列表以及自动填充的问题.假设我有 2 张纸，在一张纸上我有下拉菜单可以从另一张纸中进行选择，当我使用以下方法定义单元格和范围时，这不是问题:
=OFFSET($A$19;;;COUNTA('0528 - info'!$E$2))..但是当我想在它们之间添加一些新单元格时怎么办，以便可以自动识别它们属于哪个组:
如您所见，例如 B 列有一些组"，您可以在其中找到更多不同的值"，例如 C 列，例如电源有 MV1 和 MV2...等等.我在工作表 1 上的下拉列表与这个组"完全一样，我确实使用给定的函数手动引用了它们.但是，当我在此表中的 MV2 下方添加例如 MV3 时，是否可以自动填充我的下拉列表?还是第14排的RN7?每次我添加新值时，我都必须扩展我的下拉列表(这很好..)，但问题是当我将这张表分享给其他人时，他们会 90% 忘记它.
我希望你明白我的意思，任何建议都可以！
附言间接不以它应该的方式工作 - 它为我提供了列中的所有实例，但没有提供我需要的特定实例.
=INDIRECT("Table4[VarEDS]")这个选项再次给了我我之前已经拥有的东西 - 来自列的所有匹配"，但仍然不仅仅是特定组的匹配....
经过几天的搜索和尝试，我得到了我想要的东西 - 一点也不乏味.需要借助 Leyla (Xelplus) 的几个视频来组合更多功能:
I got a problem with some popular Excel question, dynamic ranges and data validation drop-downs and auto-populate. Lets say I got 2 sheets, and on one sheet I got drop-downs to choose from another sheet, and that is not a problem when I define cells and range using:
=OFFSET($A$19;;;COUNTA('0528 - info'!$E$2))
..but what about when I wanna add some new cells in between,so that they can be automatically recognized in which group they belong:
As you see for instance Column B has some "groups" where you can find more different "values" like in Column C, like Power Supply has MV1 and MV2... and so on. My drop-downs on the sheet 1 are called exactly like this "groups" and I did reference them manually using given function. But is it possible to populate my drop-downs automatically when I add for instance MV3 beneath MV2 in this table? Or RN7 on 14th row? Everytime I add new values I have to extend my dropdowns (what is fine..), but problem will be when I share this table to others, they gonna forget it 90%.
I hope you get my point, any suggest will be fine!
p.s. Indirect doesnt work in a way it should - It gives me all instances from the Column but not specific ones that I need.
=INDIRECT("Table4[VarEDS]")
Well this option gave me again what I already had before - all "matches" from the Column and still not ONLY matches that are for certain group. ...
After some days of searching and trying I got what I wanted - wasnt wasy job at all. Needed to combine more functions with the help of couple of videos from Leyla (Xelplus):
https://www.youtube.com/watch?v=gu4xJWAIal8
https://www.youtube.com/watch?v=7fYlWeMQ6L8&t=5s
First step was to make unique list of my values (text in my case) on separate sheet:
=IFERROR(INDEX(t_VarGroup[Vargrouptext];MATCH(0;INDEX(COUNTIF($J$2:J2;t_VarGroup[Vargrouptext]););0));"")
Then I needed to "extract" all the values that are belonging to the certain unique values:
=@IF($I3<COLUMNS($K$2:K$2);"";INDEX(t_EDS[[VarEDS]:[VarEDS]];AGGREGATE(15;3;(t_VarGroup[[Vargrouptext]:[Vargrouptext]]=$J3)/(t_VarGroup[[Vargrouptext]:[Vargrouptext]]=$J3)*(ROW(t_VarGroup[Vargrouptext])-ROW(t_VarGroup[[#Headers];[Vargrouptext]]));COLUMNS($K$2:K$2))))
FUrthermore, I created Unique drop down list:
=OFFSET($J$3;;;COUNTIF($J$3:$J$14;"?*"))
And then dependent drop down list nearby using:
=OFFSET($K$2;MATCH($H$2;$J$3:$J$17;0);;1;COUNTIF(OFFSET($K$2;MATCH($H$2;$J$3:$J$17;0);;1;20);"?*"))
And because I made it on other sheet, I had to reference them to an appropriate sheet name where my main sheet is - with drop downs, it is actually very useful for my future work and for everyone else who has struggling with drop downs but on a bit specific way =))
credits to: @Naresh Bhople for suggestion about Youtube videos.
这篇关于Excel 帮助:动态范围 + 数据验证的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
