Multi dimensional Pivot table data issues. Possibly controlling generated tables.

I have data with 4-6 dimensions and need to express this on a pivot table. Thus far I have only been able to express two dimensional data and believe that this is the extent of the tools ability. The scenario is, 32 check points, with 3 types of failures and if a failure occurs, does it cause a termination. If a failure is logged, the remaining check points will still be tested.

Solution 1.

To work around the extra dimensional data, I have created 4 pivot table s side by side and linked there common data filters with a VBA, so that i can control them all from one field change. Pvt 1 corresponds to failure 1, pvt 2 corresponds to failure 2 ect.. the check points are common. When placed side by side and with redundant columns hidden, they essential function as a multidimensional pivot table. However when I use my filter field to show only data from region one of 2, I can still select producers from region 2 of 2. Also, I seem to pull more than just data corresponding to my pivot table cells, when I double click with in my fields.

Solution 2.

I have also normalized my data using a =sumif( function in to a two dimensional data table. This by far is the best solution for navigating and ease of use, however when I double click my pivot table field cells, I return to my secondary data, not my original source data... which is a function requirement. if I could use VBA or a formula to go directly back to the source data, that would be the best solution.


Granted a pivot table may not be the best solution, it was requested by my senior manager. Any help or ideas would be greatly appreciated.