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:

enter image description here

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

enter image description here 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. ...

enter image description here

Improve this question
6
  • Can you explain further (or upload a picture of) the dropdowns? I'm not sure I understand what you want to appear in them, and what the issue actually is. Commented Mar 5, 2020 at 16:10
  • Voting to close as Needing more details or clarity. In its current state, this post is both difficult to read and to understand intention, so answering would be speculative at best. Please edit your post to be more clear. Commented Mar 5, 2020 at 16:26
  • @RogB Okay, I will edit it precisely (hopefully). Gimme a minute. Commented Mar 5, 2020 at 16:31
  • @Cyril Okay, I will edit it precisely (hopefully). Gimme a minute. Commented Mar 5, 2020 at 16:32
  • @RogB Edited!! You can have a look! Commented Mar 5, 2020 at 16:40

2 Answers 2

Reset to default
5

If your Data Validation source is a "Table" as shown in your image then you can take advantage of "Table Column" Range which is dynamic. That means whenever you refer that column as NAMED range and if make changes to the column (Edit, Add, Delete) it will reflect in the referred cell.

You can use this technique even for ranges not in table. You need to NAME them with offset formula and make dynamic.

You can find dynamic address of your column as shown in the image below. Select entire column WITHOUT Header

enter image description here

Name your column data range with appropriate name as shown in image below

enter image description here

Then in Data Validation Window refer this name using F3 as shown in image below.

enter image description here

Then you can see... Even if you edit, add or delete any row in the column the data validation will change

enter image description here

enter image description here

Editing based on your comment below: If you want text from column B and Column C appear together in the validation dropdown list. Insert column in the table and join text from column B and C and then make data validation based on that column as shown in Colum D in image below

enter image description here

Finally I think I understood your question. Watch this video Excel: Find Multiple Matches & Dependent Drop Down List

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

You had this question repetitively .. stackoverflow.com/questions/59013264/…...
Thanks man but still didnt work - I had the same results even before. I edited my post again. I knew I asked before, I got bored to use manually that is reason I asked on different way with better screen shots.
It was really hard to understand your question. :).. But I think I have understood it now. So you want to autoupdate data validation in First Sheet1 which is based on column B of second sheet2 when you change value in Column C of Sheet2 .. Am I right?
Data Validation list source must be a column or a row. It cannot be a table. So, insert a column in your table. Concatenate all the cells in the rows in that column. And the make validation in Sheet1 based on that column...
Wait, you mean completely new column with concatinating Column B and C from Sheet 2?
|
1

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.

enter image description here

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.