Slicer Problem

[Paul33]

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.

 

[cyrilbrd]

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.

 

[Paul33]

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!

 

[cyrilbrd]

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.

 

[Paul33]

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.

 

[VeryForgetful]

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.