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 .

How my pivot table should look like?
I need to calculate an average of one "parameter" (like price) for every product. Becacuse there are (for now) 47 products a pivot table can handle it. I need to present the results for every chain store type. I would like the data to be presented like:
On the left (rows) there should be the chain store type (geant, tesco,....), on the top (columns) there shold be product's 1-47. And in the "data area" averages of the price (or other parameters).

Creating such a pivot table from one data range is easy but can't it really be done using more data ranges? I can't use access or other database. It just must be done with excel. I've even tried importing "external data" (from the same file) with MS Query - and this should be some workaround....but not a perfect one.

Please help! Thanks!