Summary of data from multiple worksheets

Hi,

Apologies for the relative simple nature of my query. I have 9 sheets of sales data, some with over 50,000 records. I need a way to present this information in another worksheet so I can bring up just the data based on individual brands. I understand a pivot table is needed however I cannot see how to gather data from multiple worksheets.

For example, I need to look at all the different sales for Product A, which appears in multiple worksheets. Product A is broken down to store-level, with records for number of sales in each store, one record for units and one for value (for the same store and product). Is there a way I can get the data for all of Product A in one worksheet so I can then easily add the totals across all stores by both units and value?

E.g.
Store 1 - Product A - 10 units
Store 1 - Product A - £20
Store 2 - Product A - 7 units
Store 2 - Product A - £14

Many help much appreciated, or if you can direct me to somewhere that can help that would be great.

Hi,

Provided the number of records you want to analyse don't exceed 65535 then you can use data filter on each of the nine sheets and once filtered copy all the rows to a 10th sheet putting each copied range underneath the previous.

Then just analyse the data with a Pivot Table.

If you're going to do this regularly then it would be a good idea to create a macro to do all this for you automatically. But first things first. Do it manually first to confirm you can get the information you need then if you need help creating a macro, post back.

Regards