Forum Discussion
Create a slicer linking 2 pivot tables from different worksheet
I am trying to create a slicer linking 2 pivot tables from different sources as shown in the file attach. However I keep getting message that my column have duplicate values. Basically I am trying to link my Q data and RF data for each month. My slicer should be showing the catch which are of 3 types.
Where did I go wrong here.
Liphor There were a couple of issues in your file:
- The Append query contained two columns because you renamed the Catch column in one of its sources
- The append query then needed a filter for unique values
- The pivot tables need to be using the fields from the unpivoted table queries, not from the original tables
- The Catch field for each pivot needs to be taken from the Appended Catch query, NOT from the table or the unpivoted table query
- The slicer then needs to be on that Catch field and tied to both pivot tables.
See attached.
9 Replies
Liphor This requires a number of queries:
- a query for each data table to unpivot the source data
- a query on each table which extracts the unique Catches
- a query that appends the two previous queries and keeps the unique Catches (this query will be used to filter both pivot tables)
Make sure all queries are part of the data model.
Now create a pivot table using the data from either of the two queries in (1). Set the page filter of that pivot to the Catches field of query (3). Let Excel detect the relationships. Repeat this step for the other (1) query.
Now insert a slicer for the pivot filter field. Set the report connection of that slicer to both pivot tables.
The end result is the attached.
JKPieterse I try to follow your instructions but I am not able to link the splicer to one of the pivot even though I have managed to connect both tables. Attached is my file.
- You seem to have uploaded an empty workbook?
- Thank You for your suggestion. I studying your proposal and will get back to you as I am still quite a novice in using excel.
