productivity consolidation work sheet for a 32 page workbook

[Guest]

I need assistance with a formula...

I have a workbook that tracks work performed for each day of the month.
there are 32 sheets 31 for each day of the month and 1 for productivity
report which take the totals for each day and puts it on to one page. Column
A is the permit type which is type 1 thru 9. What I want to do now is on a
new work sheet in the same workbook I would like to, for each day in the
month, populate that page so it will tell me how many of each type of permit
were done on a specific day. For example on day 1 the person processed 4 type
1's, 6 type 2's and so on and so on.

I would like the worksheet to auto populate based on the information
provided on the other 31 worksheets

Thank you for your help.

 

[Roger Govier]

Hi Richard

One way
Assuming your sheets are named 1,2,3 etc., create a list somewhere on your
Summary sheet and give it a Name e.g. List
Insert>Name>Define>Name List Refers to Summary!$H$1:$H$31

On your Summary sheet, enter the numbers 1 through 9 in cells A1:A9
In B1 enter
=SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!$A:$A"),
$A1,INDIRECT("'"&list&"'!$A:$A")))/$A1

Copy down through B2:B9

 

[Guest]

I couldnt get it to work. I found the name list but whatever i enter tells me
it is not valid and do i enter Summary!$H$1:$H$31 into the "refers to " cell.
Actually on my sheet its called "tracking" also i get and error of #NAME? in
the cell i put the formula into

 

[Peo Sjoblom]

You don't need to use a defined name if you don't know how to define it, you
can hard code the range instead

=SUMPRODUCT(SUMIF(INDIRECT("'"&Summary!$H$1:$H$31&"'!$A:$A"),
$A1,INDIRECT("'"&Summary!$H$1:$H$31&"'!$A:$A")))/$A1

However it is easier to define a name if you just select (highlight) the
range

Summary!$H$1:$H$31

then simply type the name you want in the namebox above column A and press
enter

(if you select a cell like A2 the namebox is the box that will tell you the
cell reference of the cell you selected, it's to the left above the header
of column A)




--


Regards,


Peo Sjoblom

 

[Guest]

Ok. I dont know if I am not just getting it or what. When I enter the formula
i get an error "#REF" I am generally pretty good a creating formulas in
excell but this one has be stumped. Would it be easier for me to send you the
file to look at because maybe I am not discribing it correct

 

[Peo Sjoblom]

I can send a sample to you so you can compare it. Btw Roger has some extra
part that is not needed
and it should be COUNTIF if you want to count the different types not sum,
otherwise if your user types 6 type 2 in the sheet for day 1 it will return
12 and not 6

=SUMPRODUCT(COUNTIF(INDIRECT("'"&list&"'!$A:$A"),$A1))

and if you want to SUM them use

=SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!$A:$A"),$A1))

anyway if you want me to email you a sample post your email address but hide
it from spambots

like

mynameATNOSPAMyahooPERIODcom

which would be

(e-mail address removed)


--


Regards,


Peo Sjoblom

 

[Roger Govier]

Quite right Peo, brain fade on my part.
I wanted to do it the hard way<bg>

 

[Guest]

I used this: =COUNTIF('02'!$A$3:$A$42,"1") and worked it out appropriatly
for each type and page and it worked. thank you for your help