Problem to Team List

[gimran]

hey every one,

I have a team list where each team has around 12 -17 members. Now using Data Validation I have put the team name in a single cell. Now what i want to do is when ill select a team from the list, i want the team members info in the below table.

 

[T. Valko]

hey every one,

I have a team list where each team has around 12 -17 members. Now using Data Validation I have put the team name in a single cell. Now what i want to do is when ill select a team from the list, i want the team members info in the below table.

See if you can adapt the method described here:

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3

 

[gimran]

See if you can adapt the method described here:

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3

Sorry not working.......Having name error "#NAME?"

Any other solution?

 

[T. Valko]

Sorry not working.......Having name error "#NAME?"

Any other solution?

Ok...

If you setup your team list like this:

Book1
	A	B
1	Team	Name
2	Team1	Sue
3	Team1	Bill
4	Team1	Lisa
5	Team2	Eric
6	Team2	Paul
7	Team2	Trish
8	Team2	Carol
9	Team3	Pete
10	Team3	Don
11	Team3	Tim
12	Team3	Lee
13	Team3	Karen
14	Team3	Frank
15	Team3	Nancy
Sheet1

Then, with a drop down list in cell D2...

Book1
	A	B	C	D	E	F
1	Team	Name	_	Team	Count	Name
2	Team1	Sue	_	Team2	4	Eric
3	Team1	Bill	_	_	_	Paul
4	Team1	Lisa	_	_	_	Trish
5	Team2	Eric	_	_	_	Carol
6	Team2	Paul	_	_	_	_
7	Team2	Trish	_	_	_	_
8	Team2	Carol	_	_	_	_
9	Team3	Pete	_	_	_	_
10	Team3	Don	_	_	_	_
11	Team3	Tim	_	_	_	_
12	Team3	Lee	_	_	_	_
13	Team3	Karen	_	_	_	_
14	Team3	Frank	_	_	_	_
15	Team3	Nancy	_	_	_	_
Sheet1

Enter this formula in E2. This will return the count of records for the team selected from the drop down list.

=COUNTIF(A2:A15,D2)

Enter this formula in F2 to return the team member names:

=IF(ROWS(F$2:F2)>E$2,"",INDEX(B:B,MATCH(D$2,A:A,0)+ROWS(F$2:F2)-1))

Copy down until you get blanks.

 

[gimran]

See if you can adapt the method described here:

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3

Sorry its working fine, but i did not understand how the whole formula is actually working, can anyone explain?

 

[gimran]

Ok...

If you setup your team list like this:

Book1
*	A	B
1	Team	Name
2	Team1	Sue
3	Team1	Bill
4	Team1	Lisa
5	Team2	Eric
6	Team2	Paul
7	Team2	Trish
8	Team2	Carol
9	Team3	Pete
10	Team3	Don
11	Team3	Tim
12	Team3	Lee
13	Team3	Karen
14	Team3	Frank
15	Team3	Nancy
Sheet1

Then, with a drop down list in cell D2...

Book1
*	A	B	C	D	E	F
1	Team	Name	_	Team	Count	Name
2	Team1	Sue	_	Team2	4	Eric
3	Team1	Bill	_	_	_	Paul
4	Team1	Lisa	_	_	_	Trish
5	Team2	Eric	_	_	_	Carol
6	Team2	Paul	_	_	_	_
7	Team2	Trish	_	_	_	_
8	Team2	Carol	_	_	_	_
9	Team3	Pete	_	_	_	_
10	Team3	Don	_	_	_	_
11	Team3	Tim	_	_	_	_
12	Team3	Lee	_	_	_	_
13	Team3	Karen	_	_	_	_
14	Team3	Frank	_	_	_	_
15	Team3	Nancy	_	_	_	_
Sheet1

Enter this formula in E2. This will return the count of records for the team selected from the drop down list.

=COUNTIF(A2:A15,D2)

Enter this formula in F2 to return the team member names:

=IF(ROWS(F$2:F2)>E$2,"",INDEX(B:B,MATCH(D$2,A:A,0)+ROWS(F$2:F2)-1))

Copy down until you get blanks.

Thanks for the help. This one is rather easy to understand. If possible please explain the previous array formula how it is actually working.

 

[T. Valko]

Thanks for the help. This one is rather easy to understand. If possible please explain the previous array formula how it is actually working.

=IF(ROWS(F$2:F2)>E$2,"",INDEX(Value,SMALL(IF(Item=D$2,ROW(Item)),ROWS(F$2:F2))))

The IF function tests the range for the criteria and if it is found it returns the row number.

The row numbers are passed to the SMALL function which are in turn passed to the INDEX function to tell it where to find the results.

When the formula is copied down a column the SMALL function passes the nth smallest row number to the INDEX function. This returns the data in the order in which it was found.

 

[gimran]

=IF(ROWS(F$2:F2)>E$2,"",INDEX(Value,SMALL(IF(Item=D$2,ROW(Item)),ROWS(F$2:F2))))

The IF function tests the range for the criteria and if it is found it returns the row number.

The row numbers are passed to the SMALL function which are in turn passed to the INDEX function to tell it where to find the results.

When the formula is copied down a column the SMALL function passes the nth smallest row number to the INDEX function. This returns the data in the order in which it was found.

Thanks a lot....I need to learn a lot of things in excel.

 

[T. Valko]

Thanks a lot....

You're welcome!

I need to learn a lot of things in excel.

Me too! There's more about Excel that I don't know than there is about Excel that I do know!