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.

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
InsertNameDefineName 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
--
Regards
Roger Govier



"Florida Richard" wrote in
message ...
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.

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

"Roger Govier" wrote:

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
InsertNameDefineName 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
--
Regards
Roger Govier



"Florida Richard" wrote in
message ...
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.

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



"Florida Richard" wrote in
message ...
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

"Roger Govier" wrote:

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
InsertNameDefineName 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
--
Regards
Roger Govier



"Florida Richard" wrote in
message ...
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.

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" wrote:

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



"Florida Richard" wrote in
message ...
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

"Roger Govier" wrote:

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
InsertNameDefineName 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
--
Regards
Roger Govier



"Florida Richard" wrote in
message ...
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.

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




--


Regards,


Peo Sjoblom





"Florida Richard" wrote in
message ...
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" wrote:

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



"Florida Richard" wrote in
message ...
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

"Roger Govier" wrote:

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
InsertNameDefineName 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
--
Regards
Roger Govier



"Florida Richard" wrote in
message ...
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.