Creating dependent filters in a pivot table

[birdsoffir]

Hello,

My problem is I need to make drop down filters in pivot tables dependent and cascade as the user makes the selections. This is in Excel 2007 or lower.</SPAN>

The data table comes from a SQL query report and has many relationships in the rows. An example of the data would look like this if sorted by Col1:</SPAN>


[TABLE="width: 239"]
<TBODY>[TR]
[TD]Col1</SPAN>[/TD]
[TD]Col2</SPAN>[/TD]
[TD]Col3 </SPAN>[/TD]
[TD]Col4</SPAN>[/TD]
[/TR]
[TR]
[TD]ItemA</SPAN>[/TD]
[TD]STATE1</SPAN>[/TD]
[TD]DRUGA3</SPAN>[/TD]
[TD]DR1</SPAN>[/TD]
[/TR]
[TR]
[TD]ItemA</SPAN>[/TD]
[TD]STATE2</SPAN>[/TD]
[TD]DRUGA1</SPAN>[/TD]
[TD]DR2</SPAN>[/TD]
[/TR]
[TR]
[TD]ItemA</SPAN>[/TD]
[TD]STATE1</SPAN>[/TD]
[TD]DRUGA1</SPAN>[/TD]
[TD]DR1</SPAN>[/TD]
[/TR]
[TR]
[TD]ItemB</SPAN>[/TD]
[TD]STATE1</SPAN>[/TD]
[TD]DRUGB2</SPAN>[/TD]
[TD]DR1</SPAN>[/TD]
[/TR]
[TR]
[TD]ItemB</SPAN>[/TD]
[TD]STATE3</SPAN>[/TD]
[TD]DRUGB4</SPAN>[/TD]
[TD]DR3</SPAN>[/TD]
[/TR]
[TR]
[TD]ItemC</SPAN>[/TD]
[TD]STATE2</SPAN>[/TD]
[TD]DRUGC5</SPAN>[/TD]
[TD]DR2</SPAN>[/TD]
[/TR]
[TR]
[TD]ItemC</SPAN>[/TD]
[TD]STATE3</SPAN>[/TD]
[TD]DRUGC6</SPAN>[/TD]
[TD]DR3</SPAN>[/TD]
[/TR]
[TR]
[TD]ItemC</SPAN>[/TD]
[TD]STATE4</SPAN>[/TD]
[TD]DRUGC6</SPAN>[/TD]
[TD]DR4</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL span=3></COLGROUP>[/TABLE]

The order of selection is: for every Col1 value selected only the data populated in Col2 is viewable and available to select. If no values are populated then no selections are visible. For every Col2 selected there must be data populated in COL3 in order to be available to select. For every COL3 selected there must be a COL4 populated. Of course the drop downs would also need to work when ALL is selected. The problem I have is that pivot filters don’t have any relationship in the drop down filters so selections are available with data or without on every filter. I know this is an age old problem but I’m being asked to make the filters dependent.</SPAN>

I’ve tried using some VBA, INDIRECT and OFFSET but the problem I’m running into is that all possible solutions I’ve seen view the data as a list. Since there are many possibilites a list approach does not work. </SPAN>

Thanks for any help.

Birdsoffir