L
lurker111
Hopefully someone can help me out here:
I have a pivot chart with 3 sets of information in the "Series Fields" area.
Is there a method which can be employed to "hide" invalid options when a
higher level of data does not include the set of data from a lower level set?
Difficult to explain so here is an example:
Col X Col Y Col Z Cost
X1 Y1 Z1 $5
X1 Y1 Z2 $5
X2 Y1 Z1 $5
X2 Y1 Z3 $5
X2 Y2 Z1 $5
X2 Y2 Z3 $5
I would have "Cost" set up in the "Data Items" area. Col X, Col Y, and Col
Z are all in the "Series Fields" area. When I choose "X1" from the "Col X"
drop down menu by itself I would like to find a way to remove the options for
"Y2" and "Z3" from the "Col Y" and "Col Z" drop down menus. When I choose
"X2" from the "Col X" drop down menu (by itself) I would like for the "Z2"
option to disappear.
My actual data consists of hundreds of selections in the "Col Z" position
and this makes it difficult to selectively turn choices on and off when a
particular combination of "Col X" and "Col Y" may result in only 2 or 3 valid
"Col Z" choices.
I want behaviour similar to auto filter on a pivot chart if possible.
It seems I may have to just "hide pivot chart field buttons" and then create
a form with VB code to re-do the work done in pivot charts to behave the way
I want (drop down combo boxes populated based on settings from other combo
boxes). A few minutes playing with a built in Excel option would be
preferable to a few hours of playing with code.
Thanks in advance.
I have a pivot chart with 3 sets of information in the "Series Fields" area.
Is there a method which can be employed to "hide" invalid options when a
higher level of data does not include the set of data from a lower level set?
Difficult to explain so here is an example:
Col X Col Y Col Z Cost
X1 Y1 Z1 $5
X1 Y1 Z2 $5
X2 Y1 Z1 $5
X2 Y1 Z3 $5
X2 Y2 Z1 $5
X2 Y2 Z3 $5
I would have "Cost" set up in the "Data Items" area. Col X, Col Y, and Col
Z are all in the "Series Fields" area. When I choose "X1" from the "Col X"
drop down menu by itself I would like to find a way to remove the options for
"Y2" and "Z3" from the "Col Y" and "Col Z" drop down menus. When I choose
"X2" from the "Col X" drop down menu (by itself) I would like for the "Z2"
option to disappear.
My actual data consists of hundreds of selections in the "Col Z" position
and this makes it difficult to selectively turn choices on and off when a
particular combination of "Col X" and "Col Y" may result in only 2 or 3 valid
"Col Z" choices.
I want behaviour similar to auto filter on a pivot chart if possible.
It seems I may have to just "hide pivot chart field buttons" and then create
a form with VB code to re-do the work done in pivot charts to behave the way
I want (drop down combo boxes populated based on settings from other combo
boxes). A few minutes playing with a built in Excel option would be
preferable to a few hours of playing with code.
Thanks in advance.