Pivot Table Query

[Francis]

Pivot Table Query

Being fairly new to pivot tables, I'm sure there must be
answer to a situation we have that would ease the viewing
and analysis of our sales data.

Basically we have four divisions each selling a range of
up to 60 products and we try to keep the sales stats on a
monthly basis.

Currently we have a worksheet for each division with the
Column A being the Product type and columns B, C etc
onwards being the month of sale,

Each row represents the product being sold.

We are careful that, say row 16 on each sheet is product
ABC to maintain consistency throughout the workbook.

How can we best consolidate all four divisions into one
table to enable us to report on who sold what and when?

Are we likely to be able to achieve this by producing one
pivot table?

I have found a solution by creating a new table with the
Following headings:
Division, Month, Year, Product, Units - This creates a
long list, which gets bigger by 240 rows each month that I
can turn into a pivot table and seems to allow me to get
what I want - but I don't like the way the data needs to
be keyed and stored - Are there any other solutions, say
by creating a data entry form?

Any help you can give would be appreciated

Thanks

 

[Andy B]

Francis

From the info you provide, I reckon I would use a pivot table, using the
Multiple Consolidation option.
This will summarize the data as you wish and you can use the Page option to
display relevant information as you wish. To use pivot tables, however,
there is no need to always ensure the same information is on the same row.
This will automatically be sorted by the table.

Andy.

 

[Debra Dalgleish]

It will be easier to create reports if you enter all the sales data on
one sheet. Add columns for the sale date, and for the division name.

Then, you can sort by product, or by date. You can also filter the data
(Data>Filter>AutoFilter) to print one division's sales, or one product.

You'll also be able to create a pivot table, to summarize your sales
(Data>PivotTable and PivotChart Report)

 

[Francis]

Hi Andy, thanks for that, I can now create a pivottable
based on the four sheets I wanted, but

In using the consolidation you seem to lose the column
labels or field names so I don't seem able to assign any
of these to the page fields in the Pivottable, altough I
was sure I included that row in the consoliadted area.

Any thoughts on a way rounf this

Cheers

Francis