I'm working on a project that's trying to understand and display purchasing information (# of "purchase packages" and "$$" of those packages). The purchasing process has 5 major "stages" and there are a number of "offices". I have a raw dataset from which I've created 2 Pivot Tables (one for $$ and another for "purchase packages"), each with offices on the X axis and stages on the Y axis for both pivot tables. I've linked both tables to a single "office" data slicer and the pivot tables respond perfectly to individual and multi-selections from the slicer. Listing only the selected offices (as well as Grand Total) and the corresponding $ and packages throughout the tables. I also wanted to portray these data simultaneously on a chart with consistent formatting ($ in green and packages in blue for this multi-Y axis line table). I also always wanted to portray each of the potential stages across the X axis. Since I want to maintain the chart formatting (colors, etc), I am generating a "standard" table which shows the grand total ($ and "packages") based on each of the 5 POTENTIAL stages. I say potential because any one office may not have all 5 stages in their raw data, so a data slicer selection for Office A may only contain stages 1,3 and 5 for example. In fact, the 4th stage is not currently present in any record for the Region. So my standard table lists each of the potential stages 1,2,3,4,5 as referenced in column F (rows 23-28: Shown in red in attached images) and I use VLOOKUP to find the Grand Totals for any current slicer selections and also return the appropriate column of data based on finding the current column number for a given stage with MATCH, referring back to my list of the names of potential stages (F23-28, red). Everything works fine if I select one or any number of the first 6 offices, but starting at the 7th office ("KAI" - there are 13 total), the remaining offices selection return zeros (in the standard table) for both $ and packages, even though the pivot tables continue to portray the correct results themselves. Even stranger is that if I select multiple offices (including any combination of office 7-13 with or without office 1-6) the result works fine. Just singular selections in office 6-13 fail in the standard table (and therefore chart). Ugh! Crazy. Anyone have any ideas?All is good in the world.jpg
Formula looking for Grand Total and the Planning Stage (F23) in the packages table (which spans I4-O18), the Planning stage name being in Row 4.
=IF(ISNA(VLOOKUP("Grand Total",$I$4:$O$18,MATCH($F$23,$I$4:$O$4,0))),0,VLOOKUP("Grand Total",$I$4:$O$18,MATCH($F$23,$I$4:$O$4,0),FALSE))
Working selection from Slicer.jpg
NOT Working selection from Slicer.jpg
I would appreciate any feedback.
Bookmarks