Pivot Table - consolidating ranges (again)
 

This is my first post here...I hope that i've chosen the right
category....
My problem is similiar to other people's when it comes to consolidating
ranges with Pivot Table (sorry for my english...). I've searched the
forum but I haven't found either solution or workaround for my
problem.

My data is divided into 2 sheets, because i need to use more than 255
columns (about 433). It looks like this:
col1(ID) col2(Chain store category) col3(address) col4(date)
col5-col255 (product's data). One product uses 9 columns like: space on
a shelf, price, comments....

Bacause there are more product's I need to report, I've spliited the
data into 2 sheets. So the second sheet has the first 4 columns the
same as in the first one (a copy) and from col5-col178 other product's
data.

When you create Pivot Table from multiple consolidation ranges, you
won't get the same pivot table layout that you'd get from a single
range. You can get only a field named "Rows" and one named "Columns" -
in other words, it doesn't create fields named after the column headers

You could create another worksheet, with the fields from the first two
sheets that you want to use in the pivot table. Then, base the pivot
table on the new worksheet's data.

xman wrote:
This is my first post here...I hope that i've chosen the right
category....
My problem is similiar to other people's when it comes to consolidating
ranges with Pivot Table (sorry for my english...). I've searched the
forum but I haven't found either solution or workaround for my
problem.

My data is divided into 2 sheets, because i need to use more than 255
columns (about 433). It looks like this:
col1(ID) col2(Chain store category) col3(address) col4(date)
col5-col255 (product's data). One product uses 9 columns like: space on
a shelf, price, comments....

Bacause there are more product's I need to report, I've spliited the
data into 2 sheets. So the second sheet has the first 4 columns the
same as in the first one (a copy) and from col5-col178 other product's
data.

When you create Pivot Table from multiple consolidation ranges, you
won't get the same pivot table layout that you'd get from a single
range. You can get only a field named "Rows" and one named "Columns" -
in other words, it doesn't create fields named after the column headers


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thank you for your reply.
At first I didn't like your solution, because it would make me create
additional 9 sheets (there are already 10). I thought it would be hard
to browse through the sheets.
But later I realized that these new sheets could be hidden! And this is
the way I'm going to solve it.
I don't have much time to "fight with it". Maybe in 2 weeks time I will
come back to this problem and find another solution....
If so, I'll post my findings.
Thank you again.


--
xman
------------------------------------------------------------------------
xman's Profile: http://www.excelforum.com/member.php...o&userid=27315
View this thread: http://www.excelforum.com/showthread...hreadid=468139

You're welcome! Thanks for letting me know how you're going to solve the
problem (for now!)

xman wrote:
Thank you for your reply.
At first I didn't like your solution, because it would make me create
additional 9 sheets (there are already 10). I thought it would be hard
to browse through the sheets.
But later I realized that these new sheets could be hidden! And this is
the way I'm going to solve it.
I don't have much time to "fight with it". Maybe in 2 weeks time I will
come back to this problem and find another solution....
If so, I'll post my findings.
Thank you again.




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html