Pivots & Slicers - hiding data (without filters!) v2010

[gigir]

Hi, does anyone have any ideas on how to omit data from a pivot table (and therefore the slicers based on the pivot)
*without* using a filter on the pivot table....as that does not work (i.e. this causes a filter to be made on the slicer, so the data can be viewed if someone changes the filter - that's not what I want...I need the info to not display at all (it's a type of dashboard)).

More info:
I want to omit two types of things:
1. rows which appear as "0" all the way across - note this is not a calculation, it's "0" as a result of a copy/paste LINK...I need to capture new info in the pivot....so I have copied/pasted link from different tabs to one tab in order to base the pivot & slicers on the total info.
2. rows which have particular text (manually entered) as values across 3 different columns (happens to be "NtApp", but it could be anything)

Hope someone can help, it's driving me nuts.

 

[gigir]

I don't think I can upload an XLS here? Might be easier to show you...?

 

[VeryForgetful]

It's going to be easier to upload a sample of your data I think.

My suggestion would be to another column to your source data with a formula that evaluates to "yes" if the 2 conditions above are met else evaluates to "no". If you add these to the rows or columns area of the pivot and untick them it will appear like they are not actually there. You could also create a page field and choose to hide the "yes" data which will achieve the same outcome. You could then hide the page field row in the sheet if required and with a bit of vba prevent the row being unhidden by anyone.

 

[gigir]

Thank you VeryForgetful for your reply. Can't see how I can upload a file though - am I missing something obvious here?

I will try adding a calculated column based on the conditions, and see if I can do anything with that, thanks for that suggestion... It's the Slicers that are appearing on the dashboard/summary tab, not the actual Pivot - the pivot is just a basic select all pivot in the background, so that I could create the Slicers. So I don't have any data selected in the Pivot to display rows/columns etc.

 

[VeryForgetful]

With Slicers though you manually specify which ones you want to include. Are you using the PivotTable to show the data or is it a chart or something? Either way you need to find a way of omitting the data from your table in the first place. The slicers will only filter on what is shown in the Source pivottable.

No sure about uploading but you can probably do it through Dropbox or something like that.

 

[gigir]

I've put a sample of what I mean on Dropbox: https://www.dropbox.com/s/clrs9xrh0h8fisg/sample.xlsx?dl=0

The pivot is only for the purpose of having the slicers...and I can only specify at column header level what I choose in the slicers.

 

[VeryForgetful]

I can see what you are trying to do but slicers will only work with PivotTable data. You can't create a dummy PivotTable and expect the slicers to filter your source data as far as I know.

Where you have 3 theater tabs, can that not be moved into 1 tab? If not to construct a PivotTable you are going to have to select multiple data ranges which personally I've never had any luck with.

This should do what you want but it depends if you can live with a single 'data' tab. Have a quick look at the attached, I can't put slicers on as I'm using Excel 2003 but this should stop any of the unwanted stuff showing in the slicers when you add them. You will probably have to re save the file as xlsm before adding the slicers.

https://www.dropbox.com/s/1wvugmnzb9uejfp/Xl0000004.xls?dl=0

 

[gigir]

I don't think I was clear enough, sorry - the first tab is meant for someone else to view info as a type of dashboard, without viewing the source. So the slicers just work off each other, not to filter the pivot which remains unseen....for example user wants to see which companies have the meeting scheduled...or what's the status for a particular company (the actual file has a lot more data, and the slicers working off each other makes sense).

The different theatre tabs have been grouped in the Data tab, that's what the pivot is based on (indeed I could not get the 3 separate sources to work). That's why there are "0" rows included in the source (Data tab) - to allow for more rows to potentially be added to each theatre tab. Does that make sense? I added more clarification in an updated version of the file on Dropbox.

But really *thank you so much* for the suggestion and for creating that version for me - really appreciate it, and I like how you used the Count & the macro too. Thanks!
I can't insert slicers in it though, even after saving as, but I will try to work out something based on this - maybe even keep with pivot on the dashboard/overview instead of the slicers...will keep at it.

Thanks again!