0 Replies Latest reply: Aug 30, 2011 1:25 PM by Matt Kitchen RSS

    Help with Pivit Table

      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.