Dynamic Conditional Drop Down Lists

[dirtywizard]

H

I'm working with Excel 2010 have a lookup table for a report I'm trying to build that looks like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Campaign[/TD]
[TD]Channel[/TD]
[TD]Cell[/TD]
[TD]Sub Cell[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]#1[/TD]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]#2[/TD]
[TD]B[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]#3[/TD]
[TD]C[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]#1[/TD]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]#2[/TD]
[TD]B[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]#1[/TD]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]#2[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]#3[/TD]
[TD]C[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]#4[/TD]
[TD]D[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]#5 [/TD]
[TD]E[/TD]
[TD]E[/TD]
[/TR]
</tbody>[/TABLE]

My aim is to make 4 drop down lists containing de-duped values for each column (e.g. the 'campaign' list would be "A,B,C"), with the added wrinkle that the drop down lists should be conditional and only see the values that are relevant (e.g. if campaign "A" was selected, the drop down list for 'channel' would not have "#4, #5 " in it, because these are invalid options within the table). It's also worth noting this table will change over time with new records being added.

So far I've made four pivot tables (one for each column), and dynamic named ranges which i'm currently using for the drop downs. This gets me part of the way in creating de-duped lists for each column, but doesn't include the critical step to make the lists conditional.

How can I achieve these dynamic, conditional drop down lists? (happy for a VBA solution, though would prefer the lists themselves to be done via data validation as the end users are non-technical)

Thanks for taking the time to read this!

 

[Fluff]

Have a look here https://www.contextures.com/xlDataVal02.html

 

[dirtywizard]

Have a look here https://www.contextures.com/xlDataVal02.html

Hi Fluff

Thanks for coming back so quickly.

I've used the link you shared previously for a smaller piece of work - the problem i'll have is that this report could potentially scale to 100 or so campaigns, each with several channels, cells and sub cells.

If I imagine 10 campaigns with 10 channels each, with each channel per campaign having 10 cells which then had 10 sub cells each, the contexture example would need:

1 column to list the 10 campaigns in
10 columns to list the channels related to each campaign (1 column per campaign)
100 columns (cells per channel, 1 per channel)
1000 columns (sub cells per cell, 1 per cell)

Which unfortunately goes beyond excel's ability, hence the challenge. I could try and run multiple ranges per column, but with the table expected to grow I can foresee over-typing and all kinds of havoc in doing so. Also, having to create that volume of named ranges, even via VBA, would be challenging.

is there another way of achieving this?

 

[Fluff]

I believe that it's possible with formulae, but that's beyond my knowledge.
Whilst it's possible to do it with VBA I would advise using a Userform rather than DV.