Currently I have 6 pivot tables that are laid out the same but display different information. They are listed below.
MTD Net Sales and Net Sales Gross Profit
MTD Invoice Sales and Invoice Gross Profit
YTD Net Sales and Net Sales Gross Profit
YTD Invoice Sales and Invoice Gross Profit
Rolling YTD Net Sales and Net Sales Gross Profit
Rolling YTD Invoice Sales and Invoice Gross Profit
The time period MTD, YTD, and Rolling YTD are controlled using buttons that set vTimePeriod. Net Sales and Invoice Sales are also currently controlled using buttons that set a variable vSalesDollars.
I used conditional formatting on the layout tab to select which pivot table is shown based on the variable set by the buttons.
=IF($(vTimePeriod) = 'Year' , 1, 0 )
This works worked fine for MTD, YTD , and Rolling YTD with Net Sales but I am not sure how I can add in Invoice Sales since now my charts would be dependent on 2 variables vTimePeriod and vSalesDollars.
In a straight table I would use the presentation tab and set conditional formats for each column but that isn't an option in pivot tables. I also like this option because it would reduce the number of pivot tables I would have to create and make disappear and reappear. Any advice or tips would be great.