Dynamic drop-down menus with PivotCharts

[Josip.2000]

Hi!

The data I have can be categorized for example in two categories;
category X and Y (+ time)
The problem however is that I would like to have some kind of dynamic
drop-down menu for my second option (category Y) which changes based
on the choice of X.

The problem is that right now, I have 20 X items, but let's say 1000 Y
items, so to go through the whole list of 1000 is a bit tedious when
making selections. So I was thinking, that a much more elegant
solution would be to have the content of the Y menu change based on my
choice of X, thus narrowing the list to about 50 (if the Y's are
evenly distributed/connected to the different X's)

To me it sounds like a simple piece of code, but can it be done?
Examples?

BR,
Josip

 

[ShaneDevenshire]

Hi,

The answer is yes, but the problem is how do you decide what items to show
in the second drop down when there are 50 possible lists?

One why, using data validation list for your drop down is to attach code to
the worksheet_change event which responds to a change in the data validation
cell by choosing from one of the 50 lists for Y.

So we are back to the first question.

 

[Lisa]

Josip,
Did you get this figured out? I'm trying to do the same thing with 5
drop-downs connected to one another. Any advice or examples would be helpful.

Thanks,
Lisa

 

[ShaneDevenshire]

Hi,

I am sending a file with an example. Note the range names and the data
validations in columns A and B.

 

[Lisa]

Shane,
Great! Any help would be appreciated. I don't see your example file in
your post, do I have to go some where else to get it?

Lisa

 

[ShaneDevenshire]

I can't attach a file here, but if you send me an email I will attach the
file when I get home:

(e-mail address removed)

 

[Tushar Mehta]

For a solution that works with data organized in a relational data
format see
Cascading queries
http://www.tushar-mehta.com/excel/newsgroups/cascading_dropdowns/index.html


On Tue, 19 Aug 2008 08:03:01 -0700, Lisa

Josip,
Did you get this figured out? I'm trying to do the same thing with 5
drop-downs connected to one another. Any advice or examples would be
helpful.

Thanks,
Lisa

Hi!

The data I have can be categorized for example in two categories;
category X and Y (+ time)
The problem however is that I would like to have some kind of dynamic
drop-down menu for my second option (category Y) which changes based
on the choice of X.

The problem is that right now, I have 20 X items, but let's say 1000 Y
items, so to go through the whole list of 1000 is a bit tedious when
making selections. So I was thinking, that a much more elegant
solution would be to have the content of the Y menu change based on my
choice of X, thus narrowing the list to about 50 (if the Y's are
evenly distributed/connected to the different X's)

To me it sounds like a simple piece of code, but can it be done?
Examples?

BR,
Josip

Regards,

Tushar Mehta
Microsoft MVP Excel 2000-2008
www.tushar-mehta.com
Tutorials and add-ins for Excel, PowerPoint, and other products