EXCEL - Pivot table based on multiple ranges

[Diezz]

Hello,

I'm trying to build a pivot table based on more ranges, in the same workbook but in different sheets.

The problem is that after i select all the ranges and i try to build the layout of the PT, it won't show me the columns of the tables that i join in it.

My 5 sheets have 5 identical tables, only data varies, so i have the same column headers (i'm trying not to join all of them in the same sheet).

How can i make them appear in my PT without joining sheets?

 

[KenWright]

You can't - It's a right Royal pain, but i wouldn't even consider using disjointed ranges to feed a Pivot table. I would always consolidate and then use that consolidated sheet. Hard to say without knowing your data, but does each of your sheets expand? as if not then just link them all into a single sheet and work from that.

Even if they do expand, can you bound by how much they are likely to expand and then just allow sufficient expansion room in the linked ranges to cover new data. Blank rows won't usually matter in the Pivot Table source data, though you must have a header for every column that you include in the range.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

...which is one of the MANY reasons that similar data ought to be in a SINGLE TABLE, rather than scattered around on different sheets.

Skip,
[sub]
[red][/red]
[/sub]