Excel帮助:动态范围+数据验证 [英] Excel Help: Dynamic range + Data Validation

问题描述

我遇到了一些流行的Excel问题，动态范围和数据验证下拉列表并自动填充的问题.可以说我有2张纸，在一张纸上有下拉菜单可供选择，而当我使用以下方法定义单元格和范围时，这不是问题:

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:

例如，您看到列B具有一些组"，在其中您可以找到更多不同的值"，例如在列C中，例如Power Supply具有MV1和MV2 ...等.我在工作表1上的下拉菜单被称为组"，并且确实使用给定的功能手动引用了它们.但是，当我在此表的MV2下添加例如MV3时，是否可以自动填充我的下拉菜单?还是第14行的RN7?每当我添加新值时，我都必须扩展下拉菜单(没关系..)，但是问题是当我与其他人共享此表时，他们会忘记它的90％.

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.间接不能以应有的方式工作-它为我提供了列"中的所有实例，但没有我需要的特定实例.

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. ...

推荐答案

经过几天的搜索和尝试，我终于得到了想要的东西-根本不是一件很浪费的工作.需要在Leyla(Xelplus)的几个视频的帮助下将更多功能结合在一起:

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 =))

提供有关@Youtube视频的建议的@Naresh Bhople.

credits to: @Naresh Bhople for suggestion about Youtube videos.

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