How to get list based on data validation

[manish_1112]

Hi - Here is a problem I'm facing in creating a dashboard. I have Project's name in column B and have further teams under it listed in column C (number of teams varies for diff projects).

I have created a data validation list in CD2. What I want it is - if I select a project name in that cell I should get the list of the teams populated in CF. Excel should automatically take the number of teams and give that output. (currently, I have shown it manually to explain what it should look like). See the pic & excel attached.

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[bosco_yip]

Or, try this without helper column.

In CF3, array formula (confirm enter with SHIFT+CTRL+ENTER) copy down :

=INDEX(C:C,SMALL(IF(LOOKUP(ROW($2:$22),ROW($2:$22)/(B$2:B$22>0),B$2:B$22)=CD$2,ROW(C$2:C$22),4^8),ROWS($A$1:$A1)))&""

Regards
Bosco

 

[manish_1112]

Thanks a lot guys !