Creating dependent filters in a pivot table

birdsoffir

New Member
Joined
Dec 29, 2014
Messages
1
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>


Col1</SPAN>Col2</SPAN>Col3 </SPAN>Col4</SPAN>
ItemA</SPAN>STATE1</SPAN>DRUGA3</SPAN>DR1</SPAN>
ItemA</SPAN>STATE2</SPAN>DRUGA1</SPAN>DR2</SPAN>
ItemA</SPAN>STATE1</SPAN>DRUGA1</SPAN>DR1</SPAN>
ItemB</SPAN>STATE1</SPAN>DRUGB2</SPAN>DR1</SPAN>
ItemB</SPAN>STATE3</SPAN>DRUGB4</SPAN>DR3</SPAN>
ItemC</SPAN>STATE2</SPAN>DRUGC5</SPAN>DR2</SPAN>
ItemC</SPAN>STATE3</SPAN>DRUGC6</SPAN>DR3</SPAN>
ItemC</SPAN>STATE4</SPAN>DRUGC6</SPAN>DR4</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL span=3></COLGROUP>

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
 
Last edited:

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Watch MrExcel Video

Forum statistics

Threads
1,109,451
Messages
5,528,827
Members
409,839
Latest member
akashsadhu
Top