# Thread: Ugh, this is too tough for me to figure out!! Thanks: 0 Likes: 0

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

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.

2. ## Re: Ugh, this is too tough for me to figure out!!

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?

3. ## Re: Ugh, this is too tough for me to figure out!!

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.

4. ## Re: Ugh, this is too tough for me to figure out!!

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 C D E F G
1 Group1 Group2 Group3
2 Sue JOHN Vanessa
3 Sara LARRY Pete
4 Bert LUCY Fred
5 Group1 Alf GINA
6 FRANK
7 Alf ANNA
8
Sheet2

Workbook Defined Names
Name Refers To
Group1 =Sheet2!\$E\$2:\$E\$7
Group2 =Sheet2!\$F\$2:\$F\$7
Group3 =Sheet2!\$G\$2:\$G\$7

5. ## Re: Ugh, this is too tough for me to figure out!!

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.

6. ## Re: Ugh, this is too tough for me to figure out!!

Option 1 -

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

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