Need my Dashboard to change when slicer selected on pivot

Hi I am looking for some help, I have made a start to my dashboard and want it to show me totals of whatever sector is selected.

at the moment I can only get Clyde by date/s - I would like it to be able to change to what sector and date selected

also is there a better way to show it other than using slicers

please help

Dougers1

For the cell in D6 you need to eliminate the "Sector","Clyde Sector", piece. The slicer does that so you shouldn't reference it twice. The formula in that cell should look like:
=GETPIVOTDATA("Status",'Sector View'!$A$4,"Status","Filled")
Then it works with the slicers. The other fields are problematic because the pivot table has the fields you are referencing as rows. Try making them columns.

I remember trying to do dashboards with charts from Pivot Table and sometimes getting into "trouble" with GetPivotData, so I used actual ranges (eg H2:H10) or I stopped using Pivots and used Tables instead.
Yes, Pivots are useful, but in this case you could use simple Tables in my opinion.

In your sample file I created a Table (with CTRL+T) in your Data sheet (Table1), added slicers for Sector and Date and in the Dashboard sheet I added the Table version slicers. Then added formulas to count data in Table1 when it is filtered via slicers and I got all the information.

See attached file. Let me know if it's okay like that.

Originally Posted by DavidBowman

For the cell in D6 you need to eliminate the "Sector","Clyde Sector", piece. The slicer does that so you shouldn't reference it twice. The formula in that cell should look like:
=GETPIVOTDATA("Status",'Sector View'!$A$4,"Status","Filled")
Then it works with the slicers. The other fields are problematic because the pivot table has the fields you are referencing as rows. Try making them columns.

try the column bit but still struggle to get other data to update ?

Slicers work by default with Tables and Pivots.
Of course if the Tables are made as stated using CTRL+T and not just adding cell borders like in Word

For work I made dashboards with charts using a combination of Table and Pivot, but didn't make Pivot charts because I ran into some formatting problems - for some reason when working with Pivot charts and Slicers, the formatting of the chart wouldn't be kept between various Slicer option changes (see animated gifs below).

So I made my charts manually by adding series from ranges of data "behind" the Pivot tables.

1 - Pivot chart made from Pivot table - you can see that if you select 2 years it ruins the formatting, which remains ruined even after going back to one year selection (data range is PivotTable2)

pivot.gif

2 - the chart is not Pivot chart but has the range "behind" the Pivot table as source - you can see that the formatting is kept even if selecting multiple years then going back to one (data range is $O$6:$Q$18, where PivotTable2 is)

nopivot.gif

So to finally answer your question it is possible by using both Tables and Pivot tables, but it prevents some problems if you make normal charts instead of Pivot charts, even from Pivot tables as chart sources.

Just my opinion, some people here might know a solution using the Pivot chart as well.
I had to make normal charts from Pivots because I could find no other way to keep my formatting.

Originally Posted by Mrrrr

Slicers work by default with Tables and Pivots.
Of course if the Tables are made as stated using CTRL+T and not just adding cell borders like in Word

For work I made dashboards with charts using a combination of Table and Pivot, but didn't make Pivot charts because I ran into some formatting problems - for some reason when working with Pivot charts and Slicers, the formatting of the chart wouldn't be kept between various Slicer option changes (see animated gifs below).

So I made my charts manually by adding series from ranges of data "behind" the Pivot tables.

1 - Pivot chart made from Pivot table - you can see that if you select 2 years it ruins the formatting, which remains ruined even after going back to one year selection (data range is PivotTable2)

Attachment 648488

2 - the chart is not Pivot chart but has the range "behind" the Pivot table as source - you can see that the formatting is kept even if selecting multiple years then going back to one (data range is $O$6:$Q$18, where PivotTable2 is)

Attachment 648487

So to finally answer your question it is possible by using both Tables and Pivot tables, but it prevents some problems if you make normal charts instead of Pivot charts, even from Pivot tables as chart sources.

Just my opinion, some people here might know a solution using the Pivot chart as well.
I had to make normal charts from Pivots because I could find no other way to keep my formatting.

would you be able to help me a little - I also need to say that my work has excel 2010 and not latest

I'll see what I can do with the filee when I get home in the afternoon.

Thanks anything you can do will be a great help to me