I have a worksheet with separate tabs for each day of the month.

The tab/worksheet for each day has several pieces of data (all in columns)
that need to be Pivoted out to show the information per day.

For example on July 1st:
Column A - Storefront
Column B - Product
Column C - # of orders for each of the Products and Storefront defined in
A&B.

The info in these columns will change by day depending on the orders
actually made by customers. The tabs (days) are not identical with the
Storefront and Product (due to the macro the programmer put in to compile the
data from a raw data sheet)

I need to somehow create one Pivot table that will reference each tab (day)
and pull back the following data:

Storefront
Product
Date and number of orders for each Storefront/Product.

The finished Pivot Table should have (in the case of July - 31 days listed
in it) with the total orders listed by day for each storefront & product.

Any ideas on how to accomplish this?

Hi,

Rather than having separate tabs per day, you really need all the data
on the same sheet. And if the only indicator of date is the sheet name
you would also need to add a new column to your source data to indicate
the dates.

This is because pivot tables need to be based on a single contiguous
data range - i.e. on one sheet, with no blank rows and one row of column
headings. And if you want the pivot to include the data as a column or
row then you need a date column in your source data.

When working with pivots the key thing is to ensure your source data is
correctly tabulated.

HTH....Jay.


MMangen wrote:
I have a worksheet with separate tabs for each day of the month.

The tab/worksheet for each day has several pieces of data (all in columns)
that need to be Pivoted out to show the information per day.

For example on July 1st:
Column A - Storefront
Column B - Product
Column C - # of orders for each of the Products and Storefront defined in
A&B.

The info in these columns will change by day depending on the orders
actually made by customers. The tabs (days) are not identical with the
Storefront and Product (due to the macro the programmer put in to compile the
data from a raw data sheet)

I need to somehow create one Pivot table that will reference each tab (day)
and pull back the following data:

Storefront
Product
Date and number of orders for each Storefront/Product.

The finished Pivot Table should have (in the case of July - 31 days listed
in it) with the total orders listed by day for each storefront & product.

Any ideas on how to accomplish this?

Jay - thanks so much for your reply.

I'm not sure best way to pull data all into one sheet because there are
about 200+ rows per tab and then they need to be filtered based on diff
criteria.

I did create another tab and was putting individual pivot tables in for each
day...but then that still left me with the need to compile all data into one
spot. Still haven't figured out best way to do it.

It's beating me down! :-) Any other ideas?

"Jason" wrote:

Hi,

Rather than having separate tabs per day, you really need all the data
on the same sheet. And if the only indicator of date is the sheet name
you would also need to add a new column to your source data to indicate
the dates.

This is because pivot tables need to be based on a single contiguous
data range - i.e. on one sheet, with no blank rows and one row of column
headings. And if you want the pivot to include the data as a column or
row then you need a date column in your source data.

When working with pivots the key thing is to ensure your source data is
correctly tabulated.

HTH....Jay.


MMangen wrote:
I have a worksheet with separate tabs for each day of the month.

The tab/worksheet for each day has several pieces of data (all in columns)
that need to be Pivoted out to show the information per day.

For example on July 1st:
Column A - Storefront
Column B - Product
Column C - # of orders for each of the Products and Storefront defined in
A&B.

The info in these columns will change by day depending on the orders
actually made by customers. The tabs (days) are not identical with the
Storefront and Product (due to the macro the programmer put in to compile the
data from a raw data sheet)

I need to somehow create one Pivot table that will reference each tab (day)
and pull back the following data:

Storefront
Product
Date and number of orders for each Storefront/Product.

The finished Pivot Table should have (in the case of July - 31 days listed
in it) with the total orders listed by day for each storefront & product.

Any ideas on how to accomplish this?

Excel 2007 Tables, PivotTable
Consolidate multiple Tables into Summary sheet,
as suggested by Jason.
Macro accepts unequal sized, random ordered Tables.
http://www.mediafire.com/file/2jo2yo...08_03_09a.xlsm