|
productivity consolidation work sheet for a 32 page workbook
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. |
productivity consolidation work sheet for a 32 page workbook
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. |
productivity consolidation work sheet for a 32 page workbook
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. |
productivity consolidation work sheet for a 32 page workbook
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. |
productivity consolidation work sheet for a 32 page workbook
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. |
productivity consolidation work sheet for a 32 page workbook
Quite right Peo, brain fade on my part.
I wanted to do it the hard way<bg -- Regards Roger Govier "Peo Sjoblom" wrote in message ... 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. |
productivity consolidation work sheet for a 32 page workbook
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 "Peo Sjoblom" wrote: 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. |
| All times are GMT +1. The time now is 05:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com