Validation Lists that are dependent on criteria

[Richard_D]

Hi,



Above is an image of four validation lists. I am able to populate this lists based on pivot tables that I have created from a data source, however what I would like to do is a bit more complex and I can't figure out how to do it. The third list down is a "manager" list, I would like this to only provide a list of managers are valid based on the 'channel' within the second list down. From there I would like the 4th list down (the 'seller') to only provide a list of valid sellers based on the manager in the third list down.
The idea being that the manager and seller lists will change dependent on which channel I select from the second list down. The managers that feed into each channel and sellers that feed into each manager can often change, so it would need to be a solution that is flexible enough to recognize any changes.

If anyone could help it would be much appreciated as I've been stuck on this one for a while now!

Thanks

Richard

 

[bosco_yip]

Upload a file with your input data and together with the output expected result

Regards
Bosco

 

[Richard_D]

File uploaded for you to have a look at.

 

[bosco_yip]

B29 and B30 both show Manager ??

Please clarify.

Regards
Bosco

 

[Richard_D]

Sorry about that, I've updated the file.

 

[bosco_yip]

Sorry about that, I've updated the file.

Try........

1] "Pivots" sheet, helper list for "Manager"

1.1] In B4, copied down :

=INDEX(Data!$C$2:$C$811,MATCH(C4,Data!$B$2:$B$811,0))

2] "Pivots" sheet, helper list for "Seller"

2.1] Select A4:A131 >> Copy/Paste to L4:L131

2.2] In K4, copied down :

=VLOOKUP(L4,Data!$A$2:$B$811,2,0)

2.3] Select K4:L131 >> Editing : Sort A to Z >> OK

3] "Graphs" sheet, B29 >> Data Validation >>

>> Allow : List

>> Source =OFFSET(Pivots!$C$3,MATCH($B28,Pivots!$B$4:$B$14,0),0,COUNTIF(Pivots!$B$4:$B$14,$B$15))

and copied to B16

3] "Graphs" sheet, B30 >> Data Validation >>

>> Allow : List

>> Source =OFFSET(Pivots!$L$3,MATCH($B$29,Pivots!$K$4:$K$131,0),0,COUNTIF(Pivots!$K$4:$K$131,$B$29))

Regards
Bosco

 

[Richard_D]

Hi Bosco,

I'm up to part 3 so far and for some reason the formula isn't matching the managers to the correct channel. The COUNTIF function is correctly identifying how many managers there are within the selected channel, but it seems to be the MATCH function that isn't working as intended.
Any ideas on how to resolve, and also how to make this a solution that works when additional names / data is added?

Thanks
Richard D

 

[bosco_yip]

Regarding work for when additional names / data is added, try to Google the below for further detailing,

1] Formula: dynamic list
2] VBA : dynamic list
3] Excel Table

Regards
Bosco

 

[Richard_D]

Will do. Thanks for your help Bosco.

Regards
Richard