Excel as Database - Pivot Question
 

We have several workbooks using basically the same philosophy - Column A
contains dates, and an additional 15 to 30 columns containing specific
production data for each date each spreadsheet contains its own type of
data.

Date Raw Finished $Labor $Material etc etc
1/1/06 125,000 62,300 $38,000 $52,000 250 300
1/2/06
etc
etc

In the past we have used another tab in the workbook and populated it
with formulas that will maintain a rolling seven day total, a third tab
that keeps a running total for Month-to-date, and a fourth to keep a
running total of YTD.

These tend to get quite large by the end of the year and I was
wondering if I could save size by using pivot tables for the various
totals.

My problem is how to have the 15 to 30 headsers and the summation in
each column. Excel tells me I must have the data area populated, but
when I do, my spreadsheet grinds to a halt and I get "Not Enough
Memory" errors.

I can create a pivot with a single data item and duplicate it for the
remaining 15-30 data items, but there has to be a better way.


--
DCSwearingen

Getting old, but love computers.
------------------------------------------------------------------------
DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=554314

Excel as Database - Pivot Question
 

Hi

Bring all the data to a single sheet with an additional column that
defines the data type.
Use DataFilterAutofilter on the new Type filed to just show what you
would have had on an individual tab.

No apply your Pivot Table to the this new cumulative data sheet.
For more information on Pivot Tables, take a look at Debra Dalgleish's
site and scroll down to the section on Pivot Tables.
http://www.contextures.com/tiptech.html

also Mike Alexander's site
http://www.datapigtechnologies.com/f...es/pivot1.html

--
Regards

Roger Govier


"DCSwearingen"
wrote in
message
news:DCSwearingen.29rt6q_1150924208.6467@excelforu m-nospam.com...

We have several workbooks using basically the same philosophy - Column
A
contains dates, and an additional 15 to 30 columns containing specific
production data for each date each spreadsheet contains its own type
of
data.

Date Raw Finished $Labor $Material etc etc
1/1/06 125,000 62,300 $38,000 $52,000 250 300
1/2/06
etc
etc

In the past we have used another tab in the workbook and populated it
with formulas that will maintain a rolling seven day total, a third
tab
that keeps a running total for Month-to-date, and a fourth to keep a
running total of YTD.

These tend to get quite large by the end of the year and I was
wondering if I could save size by using pivot tables for the various
totals.

My problem is how to have the 15 to 30 headsers and the summation in
each column. Excel tells me I must have the data area populated, but
when I do, my spreadsheet grinds to a halt and I get "Not Enough
Memory" errors.

I can create a pivot with a single data item and duplicate it for the
remaining 15-30 data items, but there has to be a better way.


--
DCSwearingen

Getting old, but love computers.
------------------------------------------------------------------------
DCSwearingen's Profile:
http://www.excelforum.com/member.php...o&userid=21506
View this thread:
http://www.excelforum.com/showthread...hreadid=554314

Excel as Database - Pivot Question
 

The first thing I saw on the Contextures site was what I needed. Just
sliding the "Grey Data Button" to the right gave me exactly what I
needed.

Many thanks to everyone who answers questions and points us to various
MVP sites.


--
DCSwearingen

Getting old, but love computers.
------------------------------------------------------------------------
DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=554314