Tech Support Guy banner
Status
Not open for further replies.
1 - 5 of 5 Posts

·
Registered
Joined
·
23 Posts
Discussion Starter · #1 ·
Hi all.

I have an extensive workbook that I'm trying to work out a few kinks in for 2011.

Currently I have a master data sheet that then filters through pivot tables to give more simplified information. Each pivot table contains the same row labels and values, but for different time frames. I have it set up to calculate some of the formulas for me automatically but there is one field that I need to exclude two of the ten row labels from. Currently I have a column next to the pivot table that I have calculating this weird figure but I'm wondering if there's a way I can have it be integrated into the pivot table in the event we add more row labels in the future.

Is there a way to set up the pivot table formula to exclude a certain row label?

I've included an example workbook. Let's say I'm trying to show the Grand Total for % of Cake Slices for Mon-Fri only. Is there a way to do this?
 

Attachments

·
Super Moderator
Joined
·
65,961 Posts
Normally you would just click on the dropdwon arrow for row labels and theres a set of tick boxs - so you can exlude certain row values - ie Sat & Sun
I only have excel 2003 - so i cant use the version you posted as i need to have a later version to test
 

·
Registered
Joined
·
23 Posts
Discussion Starter · #3 ·
Etaf - unfortunately unchecking one of those boxes will exclude the unchecked for all calculations. I only want to exclude one item from one of the six pivot table calculations.
 

·
Super Moderator
Joined
·
65,961 Posts
sorry I dont understand - you want to exclude sat and sun in the calculations

what else do you need to exclude

you only have one PVT table - when you create six are you using the same records/data area - often you are asked to do this to save space - what happens if you do not do this

I have had numerous PVT tables and chart showing different data from the one data sheet - but i think i ran into trouble when i kept / based on the same data
 

·
Registered
Joined
·
23 Posts
Discussion Starter · #5 ·
For the example I posted, I need to show the total of cakes, the total of guests, etc. all for Sunday - Saturday...but only on the % of Cakes column do I not want to include the weekend. I have to have multiple pivot tables to show different time periods of the total data. (ie. The week, the month, the quarter, the year).

Essentially it currently looks like this:



...but I would like for column F to not include Sat and Sun in the pivot table calculation. I can manually create a column, but if an item in column A is removed or one is added, it will not automatically add it to the manually created column.
 
1 - 5 of 5 Posts
Status
Not open for further replies.
Top