Slicer Problem

Paul33

New Member
Joined
Nov 17, 2016
Messages
7
Hi,

I have a workbook with 3 sheets, "Complaint Chart", "Pivot Tables" and "Pivot Charts".

The actual pivot tables, on the sheet Pivot Tables, are created from a dynamic named range from the Complaint Chart sheet.

I then have 3 slicers, Year, Day and Type on the Pivot Chart sheet which are linked to all my pivot tables.

The problem I have is when the userform adds new data to the Complaint Chart sheet the pivot tables refresh ok but the slicers appear to duplicate the 'filter options'. For example, The complaint chart sheet contains many rows containing the year 2016, however when new data is added to the complaint chart, with year 2016, the pivot tables refresh ok but the slicers then show 2016 twice in the 'filter options'.

I cant figure out why this is happening or how to stop it, please can anyone help?

Thanks in advance. Paul.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Hi, check formatting and formulae used to extract the year, unless those are hard coded? in which case check for trailing spaces.
One of our user had the same concern lately, was using a MAC and the wrong formulae to extract the year.
 
Upvote 0
Hi, Thanks for your reply. I'm creating a userform to make it easier to enter data into the sheet and on looking at the data already there the year is extracted from a cell (C1), using the formula =TEXT(C1,"YYYY"). The user form I have created uses the code ComplaintYear = YEAR(ComplaintDate). Complaint date is taken from the system.

Could this be the reason for the problem? If so do you have any suggestions on how I can rectify this please?

Sorry if its a bit vague, I'm new to all this!
 
Upvote 0
Is ComplaintDate equivalent to =TEXT(C1,"YYYY")?
Look into the formulae used in the raw data, the pivot and henceforth the slicers should follow accordingly.

Note: Text(ref,"YYYY") works perfectly on a MAC table to extract the year from a date, YEAR(ref) will not return the correct value unless formatted as general but the slicers will return a 1905.
 
Upvote 0
Thanks for your reply. I think it does come down to the formula used so I will have a look into these as you have suggested.

Many thanks. Paul.
 
Upvote 0
On the data tab for each PivotTable try changing 'Number of items to retain per field' to 0.

Now refresh the tables and slicers. I used to have the same problem with greyed out ghost items remaining in the slicers until I tried this.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top