Parts summary

[Cuyler]

I'm using column 'A' for our parts list, with some 45
different parts in all, and 'Row 1' for the dates of the
month, 1-31 in most cases. I want to link the raw data of
parts sold, to a summary sheet to come up with a daily
count, by part, of individual parts sold.

For example:
Column A, lists, top to bottom, parts 1 thru 45
Row 1, column B, lists dates of the month 1-30(31)
So on the 5th, I might have sales of twelve Part #44 and
three of Part #22, and so on. The sales are entered as
they come in, but only in date order. The Part numbers
can be in any order.

What's the best way to come up with a summary like this?

 

[Dave Peterson]

Your data is laid out like this:

part numbers down column A (say row 2 to row 46)
dates across row 1 (say column B to AF)

If yes, I think I'd just sort by the part number and apply Data|subtotal.
Then you can use those outlining symbols on the left hand side to hide/show
details.

Another way that would require a change to your data is to lay your data out in
3 columns.

column A = Part number
column B = date
column C = quantity
(column D-IV any details that you need).

Then you could use Data|Pivottable to get really nice summary reports.

In fact, I think I'd use one giant worksheet for this--just keep adding the data
to the bottom of the list. And enter the date completely (year, month, day).

If you're interested in pivottables:

Debra Dalgleish's pictures at Jon Peltier's site:
http://www.geocities.com/jonpeltier/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx