Formula Question

[djb_36]

Ok here's what I am trying to do.

In several worksheets, I have rows of data. There are columns that contain each of the following pieces of data (the first one is column A, second B, etc, separated by semi-colons):

COST CENTER; NAME; TITLE; PHONE; FLOOR; COMPUTER 1; COMPUTER 2; COMPUTER 3; MONITOR 1; MONITOR 2

Each worksheet is by department - each department has multiple cost centers.

What I would like to do is have a summary page with rows for each unique cost center and then counts of each hardware type. The other part is that there are some cost centers that are duplicated amongst departments (worksheets) - so I will need to formulate this across worksheets.

So ultimately I have something that looks like (columns separated by "--")

COST CENTER -- QTY PC 1 -- QTY PC 2 -- etc...
CC12345 -- 5 -- 3 -- etc...
CC54321 -- 12 -- 9 -- etc....

At the end of the day I need to know that cost center 12345 will be buying X PC1's, Y PC2's, Z PC3's, etc.

Help!!

Many thanks in advance!

 

[Norie]

Have you tried creating a pivot table by going to Data>Pivot Table Report...?

You could use COST CENTER as a row item, COMPUTER 1 etc as columns and count of COMPUTER 1 etc as data items.

 

[Smitty]

Welcome to the Board!

That sounds like a job for a Pivot Table report. Check out the helpfile, it actually documents how to set one up pretty well.

Hope that helps,

Smitty

 

[djb_36]

Thank you all - worked like a charm!

 

[djb_36]

Sorry - one other note.

Is it normal that after I added the pivot table that my Workbook ballooned to 70Mb in size? (It was only about 180K previously.)

 

[Smitty]

Not really, it may have become corrupted.

You can try clearing out unused ranges, but at 70Mb, I don't know what good it would do...

Do you have a backup copy?

Smitty

 

[djb_36]

Yes I do have a backup. But if I simply delete the worksheet with the table, it is back to normal size.

 

[Smitty]

That's pretty strange; even with large workbooks, I've never had that problem with a Pivot Table.

Maybe someone else has run into something similar.

Smitty

 

[KenWright]

How many rows/columns of data in your sheet?

 

[djb_36]

I am building the pivot table from 11 different worksheets within the same workbook. All of the worksheets have 10 columns. The largest number of rows is 105. The smallest is 7.