Dependent Data Validation - Team Roster

[ktwainscott]

Team Validation.xlsx

I need a way to have two validation boxes. In the first box it would have one option for each team leader (remove duplicate) and depending on what leader was chosen, the second validation box would list the assigned team members.

Is this possible?

[TMS]

Look at:

http://www.contextures.com/xlDataVal02.html

Regards, TMS

[npamcpp]

Dependent data validation is explained in detail here: http://www.contextures.com/xlDataVal02.html
You will need to change your data layout, so you have a list of unique names for your Leaders.

Or take a different approach. A unique list of leaders can be created by copying the Leaders and using Remove Duplicates. Then you can use the list of unique leaders in a data validation list in F2.

Define a range name called "Members" with the formula

Please Login or Register  to view this content.

Then create a data validation with the "Members" las the list source in F3.

see attached.

[dilipandey]

Hi Ktwainscott,

See the attached file where I have used dynamic names in data validation.. thanks.
Team Validation(1).xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

[npamcpp]

dilipandey, it would be nice if you could describe your approach in your post, so people are not forced to download a spreadsheet to see what you are suggesting. That would also help the search engines find a good solution.

[dilipandey]

Sure..

Hi Ktwainscott,

Below is the approach which I have used in order to provide you a solution :-
=======
I have used dynamic name member & leader in data validation:-

member:
=OFFSET(Sheet1!$B$1,MATCH(Sheet1!$E$2,Sheet1!$C:$C,0)-1,0,COUNTIF(Sheet1!$C:$C,Sheet1!$E$2),1)

leader:-
=OFFSET(Sheet1!$I$3,0,0,COUNTA(Sheet1!$I:$I)-1,1)

Leader names are sourced from a pivot table data
=====
Now, please go ahead and download the workbook if above approach appeals you. Thanks.


Regards,
DILIPandey

<click on below 'star' if this helps>

[ktwainscott]

Perfect! Both ways work brilliantly! thank you for your quick replies!

[dilipandey]

Great..!!

Please mark this thread as [SOLVED]... thanks


Regards,
DILIPandey

<click on below 'star' if this helps>