Pivot Table Jam

[john.galt.online]

I am trying to create a pivot table matrix Big grin

Essentially, I am creating pivot tables using dynamic data sources,
defining the sources using offset and counta.

I need to get these tables in a specific pattern on a single sheet.
For instance, I may in a particular case want to align 4 pivot tables
around a box. For instance, let's say we have 4 tables - A, B, C, and
D. It so happens that A's column headers are also D's column headers,
B's column headers are also C's column headers. Similarly, A's row
headers are also B's row headers, and C's row headers are also D's row
headers. I want to display this relationship on 1 sheet in dynamic
tables....by say putting an imaginary blank square in the middle, and
arranging pivot tables around it, such that A/D's column headers are
on right, B/C's on left and A/B's row headers are on top, C/D's on
bottom. so essentially, the four corners of the center square also
form corners of the the pivot table data, in that each corner of the
square/rectangle is one of the corners of one of the pivot tables. Am
I getting too verbose and unclear as to what I want? Razz If so,
please drop me a message and ask for a clarification.

So...essentially, one of the problems I guess is placing dynamic pivot
tables next to each other as Excel gets angry and says pivot tables
cant overlap if ever the data source changes to include more columns/
rows.

Secondly, for some of the tables I want to display column/row headers,
for other not.

And thirdly, I want to use a bit of conditional formatting on the
pivot tables. Not sure I can do that on dynamic pivot tables.

Please help!

 

[Guest]

Hi,

If you place them as described you will get the error. You could put them
the max distance apart and have VBA hide or unhide the rows or columns
between them automatically.

If you don't want to show the row or column headers you will need to hide
the rows or columns in question, you can't turn off their display.

You can apply conditional formatting, however, it can be tricky to do this
for a pivot table that is changing size/shape. You can write VBA code which
reformats the pivot table after every refresh. You would add the code to the
Worksheet object:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
...Your code...
End Sub