how to position pivot tables within a report sturcture

[seatejo]

I am compiling a report using multiple pivot tables with same column structure. The problem is I can't predict the number of entries for each pivot table upon data refresh to position the tables. If I leave too many rows in between, the report would look strange when there is no entries for certain pivot tables, but then it would run into error if I did not leave enough rows for the tables to expand. Is there a smarter way to do this? Advice please.

[philozelda]

that is always tough to put expanding and contracting pivot tables together on the same sheet. I recommend creating a separate sheet for each pivot table to make room and to make things easier. Always refer each pivot table to the same source data by creating a dynamic named range for the source data:

named range = sourceData and the formula would be:

Please Login or Register  to view this content.

In each of the pivot tables refer the Change Data Source to sourceData, so when you refresh the main pivot table, all of the other pivot tables will refresh along with it.

[seatejo]

thanks for the tips, I have been using dynamic named range for the source data. It's the reporting format that I am struggling. unfortunately, having all the pivot tables on separate pages is easy for me but no good for management users expecting a structured report in a single view/sheet

[philozelda]

If I understand correctly, your columns always stay the same, is that right? How many pivot tables do you have in total? Are they stacked one on top of each other downwards or do you split them up and stack them two by two, etc?