Ugh, this is too tough for me to figure out!!

Mrs. T · Aug 26, 2019

Hi,

I want to create a drop down where they select a group and all the names fill in below it.

GROUP 2
JOHN
LARRY
LUCY
GINA
FRANK
ANNA

Cell A5 is my drop down where they will select the group

Starting in A7, I'd like a formula that would look at A5 and start to list the names. Essentially, if they select group 2 then in A7 should be John, A8 should be Larry, A9 should be Lucy, etc. Then cell A13 should be blank because all the names are already listed above.

I will be drawing all this information from a large list which will be sorted by groups.

Akuini · Aug 26, 2019

I will be drawing all this information from a large list which will be sorted by groups.

Since you have a large list then maybe VBA is more suitable for you.
Are you willing to use VBA?

ekrause · Aug 26, 2019

Hello!

Happy to give it a try. This will really come down to the way your data is structured and grouped. I pulled together a few examples of what I mean.

Case 1

GROUP 2 GROUP 3 GROUP 4
JOHN DAVID ...
LARRY ANDREW
LUCY MIKE
GINA MICHELLE
FRANK SARAH
ANNA

In case 1, I would write an index-match-match with a counta partially locked on $A$7:A7.

Case 2

JOHN Group 2
LARRY Group 2
LUCY Group 2
GINA Group 2
FRANK Group 2
ANNA Group 2
DAVID Group 3
ANDREW Group 3
MIKE Group 3
MICHELLE Group 3
SARAH Group 3
... Group 4

There's a few solutions for this. If your lists are in order, you can use countif on your A5 cell in an if statement to stop the formula after it reaches a certain number of names.

Case 3

Group 2
JOHN
LARRY
LUCY
GINA
FRANK
ANNA
Group 3
DAVID
ANDREW
MIKE
MICHELLE
SARAH
Group 4

This one is the least stable IMO. You will need to have consistent group names which can be tough to maintain. Assuming you are comfortable signing up for that, index match to get the start of GROUP X then offset with a counta $A$7:A7. You'll need an if statement to check when it moves to GROUP X+1.

Some other things to think about - Will you have people in duplicate lists? For example, would JOHN ever be in GROUPs 2, 4 and 7? How often will you be updating these lists? Will they always be sorted by grouping (all of group 1, then all of group 2, etc.)? Do you know the maximum size of groups?

Lots there. If you can tell me how the data is structured I can give you a more tailored solution.

Toadstool · Aug 26, 2019

If you can put all your data into Excel Tables then this approach uses one function.

Enter your groups and data as below although they could be on another sheet. One critical factor is the headings can't contain spaces or special characters (underscore is OK).

Select all the cells with data and Insert, Table. Now select Formulas, Create from Selection and use the Top Row for names. Selecting Name Manager you should see a Table1 name (created when you did Insert Table) and the three Groupn names.

In A5 select Data, Data Validation, List and select all the headings from the table.
Now if you select the A5 LoV it should offer Group1, Group2, Group3.

In A7 select Data, Data Validation, List and for Source enter =INDIRECT($A$5)
Excel takes the A5 entry as the table name and the LoV in A7 will offer all the entries for that named range.

	A	B
Group1	Group2	Group3
Sue	JOHN	Vanessa
Sara	LARRY	Pete
Bert	LUCY	Fred
Group1	Alf	GINA
FRANK
Alf	ANNA

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Group1[/TH]
[TD="align: left"]=Sheet2!$E$2:$E$7[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Group2[/TH]
[TD="align: left"]=Sheet2!$F$2:$F$7[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Group3[/TH]
[TD="align: left"]=Sheet2!$G$2:$G$7[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Mrs. T · Aug 27, 2019

I'm not familiar with VBA so I don't think that's the route I can go.

Ok so the data is pulled where the group numbers are all listed in column A and the names are all in column C.

There is not a set limit to how many people are in each group...there could be anywhere from 5 to 30 in each group so I'm not sure the COUNTIF would work.

ekrause · Aug 27, 2019

Option 1 -

=IFERROR(INDEX('YOUR DATA SHEET NAME'!$C$1:$C$40,MATCH(0,INDEX(COUNTIF($A$6:A6,'YOUR DATA SHEET NAME'!$C$1:$C$40)+('YOUR DATA SHEET NAME'!$A$1:$A$40<>$A$5)*1,0),0)),"")

The over engineered solution based on the addresses you provided in post #1 . You'll have to adjust your ranges to match your data file. Copy down below the highest group count.

Pros - This will find all names in the group even if they haven't been sorted. Cons - Can be slow depending on how big your data set is.

Option 2

=IF(COUNTIF('YOUR DATA SHEET NAME'!$A:$A,$A$5)<=COUNTA($A$6:A6),"",INDEX('YOUR DATA SHEET NAME'!$C:$C,MATCH($A$5,'YOUR DATA SHEET NAME'!$A:$A,0)+COUNTA($A$6:A6)))

Pros - should be quick regardless of file size. Cons - Need to sort data set in Group Order for this to work.

Ugh, this is too tough for me to figure out!!

Mrs. T

Board Regular

Excel Facts

Akuini

Well-known Member

ekrause

Board Regular

Toadstool

Well-known Member

Mrs. T

Board Regular

ekrause

Board Regular

Similar threads

Forum statistics

Share this page

Ugh, this is too tough for me to figure out!!

Mrs. T

Board Regular

Excel Facts

Akuini

Well-known Member

ekrause

Board Regular

Toadstool

Well-known Member

Mrs. T

Board Regular

ekrause

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock