Filter Pivot Table using list box on table with multiple lines with list box using same data validation source

[onlibo]

Here is an interesting one for you folks looking for a challenge:

I have a table "RecurringINVReceipt" that resides on "sheet14" used to enter a list of inventory to be received that starts out empty for the user. I am running Excel 2010.

• Row 9 is the first non-header row of the table. Cell A9 is a list box that has data validated by a dynamic range that is referring to the first field "Artist" in a pivot table "ProductsRNGPVT", which resides on "sheet15";
• Cell B9 is a list box that to data validated by same pivot table as A9, but refers to the second column in that Pivot Table "Product Name";
• I would like to filter the first first field in the pivot table by cell A9 and the automatically refresh the Pivot Table after cell A9 has been updated;
• The rub is that list boxes are in a table and new list boxes are created for each row as new lines are added. So, row requires the functionality to work dynamically.

I am thinking the code needs to look at the "RecurringINVReceipt" table and column A [Artist's Name] and when any cell in [Artist's Name] becomes 'active' use the selection in that active cell as the filter value.


Any help would be very much appreciated!

Thanks in Advance,
Scott

 

[onlibo]

I ended up going with a non-pivot table solution.