update pivot tables using named ranges

I added a couple of helper pivot tables on the Compositions Sheet.

The one in column F just gets a list of unique components for the sake of data validation. I overlaid this with a named dynamic range Component_List =OFFSET(Compositions!$H$4,0,0,COUNTA(Compositions!$H:$H)-2,1)

I use this to validate cell F3 on the dashboard.

Then I wrote a change event to set the filter on the other dashboard when cell f3 is changed.

Please Login or Register  to view this content.

The results are overlaid with another dynamic range: ID_List =OFFSET(Compositions!$H$4,0,0,COUNTA(Compositions!$H:$H)-2,1)

The formula in cell F4 on the dashboard is simply =ID_List

It took a bit of juggling. I added a helper column to the sales data to see if the ids were on the list for the components. I then used that data as the filter for the pivot table on the dashboard sheet. But first I had to do a check to see if there were any matches. That's in cell I3. If there is a match, then I refresh the pivot table. If there isn't I pop up a message stating that there are no sales for the component.