Pivot tables - multiple consolidation ranges

[ashleywanless]

Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I’m having issues with a pivot chart which has source data from multiple consolidation ranges. I have 4 sheets with data in them, 5 columns in total (A:E), all 4 sheets have exactly the same format, column A is Task, B is Time, C is Business Area, D is Administrator and E is date.
<o> </o>
Now I want to use a pivot to analyse this data but when I go through the multiple consolidation steps to produce the pivot the pivot table only shows row, column and value in the pivot table field list. Why doesn’t it show my column headers in this field? Is it possible to do this as I wish to analyse task times by business area.

Would i need to write a macro or use an excel function to collate the 4 sheets into one and run the pivot from this?

Thanks
<o> </o>
Thanks

 

[ashleywanless]

Hi,

tried to import the data like that however when the master is open and i try tto open one of the feeding spreadsheets it only lets me do this read only which is a real issue. I clicked on the link you gave me above and this was a really good example to do what i needed.

Thanks again for all the help. P.S is this issue i have with importing the data common in excel?

 

[Legacy 366625]

I haven't tested this with a pivot chart, but I was able to make a consolidated pivot table with the proper column headers by making named ranges that included the headers and all of the data, and using the named ranges as the source data for the consolidated pivot tables.

For your example, you would need to go to each of your four sheets, select all of the data on the sheet including the headers, and create a unique named range for each sheet's data. This approach assumes your data has headers; this will only work if your data has headers, so if you cannot have headers, this approach will not work for you. If you're not familiar with Ctrl+Shift+[arrow keys], learn about this keyboard shortcut to quickly select your data. Once you have the named ranges, you can follow the normal procedure for making a consolidated pivot chart, referencing the named ranges as the source data. This should use your headers for the column labels.

Good luck!

Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I’m having issues with a pivot chart which has source data from multiple consolidation ranges. I have 4 sheets with data in them, 5 columns in total (A:E), all 4 sheets have exactly the same format, column A is Task, B is Time, C is Business Area, D is Administrator and E is date.
<o> </o>
Now I want to use a pivot to analyse this data but when I go through the multiple consolidation steps to produce the pivot the pivot table only shows row, column and value in the pivot table field list. Why doesn’t it show my column headers in this field? Is it possible to do this as I wish to analyse task times by business area.

Would i need to write a macro or use an excel function to collate the 4 sheets into one and run the pivot from this?

Thanks
<o> </o>
Thanks