| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
I am attempting to create a summary sheet
I am trying to sum totals from 19 pages, I have entered 2 dummy sheets at the
beginning & end & now want monthly totals. each worksheet has monthly totals but I need the overall month for the whole team, I tried sum(first:last cell number) but the answer is zero - what am I missing ? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am attempting to create a summary sheet
Exactly what did your formula look like?
-- David Biddulph "Liana" wrote in message ... I am trying to sum totals from 19 pages, I have entered 2 dummy sheets at the beginning & end & now want monthly totals. each worksheet has monthly totals but I need the overall month for the whole team, I tried sum(first:last cell number) but the answer is zero - what am I missing ? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am attempting to create a summary sheet
"David Biddulph" wrote: Exactly what did your formula look like? -- David Biddulph "Liana" wrote in message ... I am trying to sum totals from 19 pages, I have entered 2 dummy sheets at the beginning & end & now want monthly totals. each worksheet has monthly totals but I need the overall month for the whole team, I tried sum(first:last cell number) but the answer is zero - what am I missing ? =SUM(first:lastB48), I also tried sum= () to which I clicked on every persons monthly total cell & hoped to get an answer - still no luck |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am attempting to create a summary sheet
You missed out a small but important exclamation mark.
It should be =SUM(first:last!B48) -- David Biddulph "Liana" wrote in message ... =SUM(first:lastB48), I also tried sum= () to which I clicked on every persons monthly total cell & hoped to get an answer - still no luck "David Biddulph" wrote: Exactly what did your formula look like? -- David Biddulph "Liana" wrote in message ... I am trying to sum totals from 19 pages, I have entered 2 dummy sheets at the beginning & end & now want monthly totals. each worksheet has monthly totals but I need the overall month for the whole team, I tried sum(first:last cell number) but the answer is zero - what am I missing ? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am attempting to create a summary sheet
Thanks, I now have a #REF error in the cell ! should I always use the blank
pages at the beginning & end of the book ? "David Biddulph" wrote: You missed out a small but important exclamation mark. It should be =SUM(first:last!B48) -- David Biddulph "Liana" wrote in message ... =SUM(first:lastB48), I also tried sum= () to which I clicked on every persons monthly total cell & hoped to get an answer - still no luck "David Biddulph" wrote: Exactly what did your formula look like? -- David Biddulph "Liana" wrote in message ... I am trying to sum totals from 19 pages, I have entered 2 dummy sheets at the beginning & end & now want monthly totals. each worksheet has monthly totals but I need the overall month for the whole team, I tried sum(first:last cell number) but the answer is zero - what am I missing ? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am attempting to create a summary sheet
I have managed to get a total for one sum but if I try with the other 2
totals in the same way I still get a zero figure.. "David Biddulph" wrote: You missed out a small but important exclamation mark. It should be =SUM(first:last!B48) -- David Biddulph "Liana" wrote in message ... =SUM(first:lastB48), I also tried sum= () to which I clicked on every persons monthly total cell & hoped to get an answer - still no luck "David Biddulph" wrote: Exactly what did your formula look like? -- David Biddulph "Liana" wrote in message ... I am trying to sum totals from 19 pages, I have entered 2 dummy sheets at the beginning & end & now want monthly totals. each worksheet has monthly totals but I need the overall month for the whole team, I tried sum(first:last cell number) but the answer is zero - what am I missing ? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am attempting to create a summary sheet
Either you've got a different formula from what you think you've got, or
you've got different values in the input cells from what you think you've got (and one possibility in the last category is that you've got text where you think you've got numbers). -- David Biddulph "Liana" wrote in message ... I have managed to get a total for one sum but if I try with the other 2 totals in the same way I still get a zero figure.. "David Biddulph" wrote: You missed out a small but important exclamation mark. It should be =SUM(first:last!B48) -- David Biddulph "Liana" wrote in message ... =SUM(first:lastB48), I also tried sum= () to which I clicked on every persons monthly total cell & hoped to get an answer - still no luck "David Biddulph" wrote: Exactly what did your formula look like? -- David Biddulph "Liana" wrote in message ... I am trying to sum totals from 19 pages, I have entered 2 dummy sheets at the beginning & end & now want monthly totals. each worksheet has monthly totals but I need the overall month for the whole team, I tried sum(first:last cell number) but the answer is zero - what am I missing ? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am attempting to create a summary sheet
Hi Liana
Your sheet order should be Summary Sheet with your Sum formula First (Blank) Sheet1 .. .. Sheet19 Last (Blank) The Summary sheet can be before First, or after Last, but should not be in between. -- Regards Roger Govier "Liana" wrote in message ... I have managed to get a total for one sum but if I try with the other 2 totals in the same way I still get a zero figure.. "David Biddulph" wrote: You missed out a small but important exclamation mark. It should be =SUM(first:last!B48) -- David Biddulph "Liana" wrote in message ... =SUM(first:lastB48), I also tried sum= () to which I clicked on every persons monthly total cell & hoped to get an answer - still no luck "David Biddulph" wrote: Exactly what did your formula look like? -- David Biddulph "Liana" wrote in message ... I am trying to sum totals from 19 pages, I have entered 2 dummy sheets at the beginning & end & now want monthly totals. each worksheet has monthly totals but I need the overall month for the whole team, I tried sum(first:last cell number) but the answer is zero - what am I missing ? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am attempting to create a summary sheet
I am now confused - where will the text be that should be numbers etc - how
do I check that ? "David Biddulph" wrote: Either you've got a different formula from what you think you've got, or you've got different values in the input cells from what you think you've got (and one possibility in the last category is that you've got text where you think you've got numbers). -- David Biddulph "Liana" wrote in message ... I have managed to get a total for one sum but if I try with the other 2 totals in the same way I still get a zero figure.. "David Biddulph" wrote: You missed out a small but important exclamation mark. It should be =SUM(first:last!B48) -- David Biddulph "Liana" wrote in message ... =SUM(first:lastB48), I also tried sum= () to which I clicked on every persons monthly total cell & hoped to get an answer - still no luck "David Biddulph" wrote: Exactly what did your formula look like? -- David Biddulph "Liana" wrote in message ... I am trying to sum totals from 19 pages, I have entered 2 dummy sheets at the beginning & end & now want monthly totals. each worksheet has monthly totals but I need the overall month for the whole team, I tried sum(first:last cell number) but the answer is zero - what am I missing ? |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am attempting to create a summary sheet
My workbook is laid out exactly that way , I am now beginning to give up just
when I thought I was getting the hang of it all !! "Roger Govier" wrote: Hi Liana Your sheet order should be Summary Sheet with your Sum formula First (Blank) Sheet1 .. .. Sheet19 Last (Blank) The Summary sheet can be before First, or after Last, but should not be in between. -- Regards Roger Govier "Liana" wrote in message ... I have managed to get a total for one sum but if I try with the other 2 totals in the same way I still get a zero figure.. "David Biddulph" wrote: You missed out a small but important exclamation mark. It should be =SUM(first:last!B48) -- David Biddulph "Liana" wrote in message ... =SUM(first:lastB48), I also tried sum= () to which I clicked on every persons monthly total cell & hoped to get an answer - still no luck "David Biddulph" wrote: Exactly what did your formula look like? -- David Biddulph "Liana" wrote in message ... I am trying to sum totals from 19 pages, I have entered 2 dummy sheets at the beginning & end & now want monthly totals. each worksheet has monthly totals but I need the overall month for the whole team, I tried sum(first:last cell number) but the answer is zero - what am I missing ? |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am attempting to create a summary sheet
Hi Liana
Don't give up, it will work. If you want to mail me a copy of your workbook, I will take a look. to mail direct send to roger at technology4u dot co dot uk Change the at and dots to the obvious. -- Regards Roger Govier "Liana" wrote in message ... My workbook is laid out exactly that way , I am now beginning to give up just when I thought I was getting the hang of it all !! "Roger Govier" wrote: Hi Liana Your sheet order should be Summary Sheet with your Sum formula First (Blank) Sheet1 .. .. Sheet19 Last (Blank) The Summary sheet can be before First, or after Last, but should not be in between. -- Regards Roger Govier "Liana" wrote in message ... I have managed to get a total for one sum but if I try with the other 2 totals in the same way I still get a zero figure.. "David Biddulph" wrote: You missed out a small but important exclamation mark. It should be =SUM(first:last!B48) -- David Biddulph "Liana" wrote in message ... =SUM(first:lastB48), I also tried sum= () to which I clicked on every persons monthly total cell & hoped to get an answer - still no luck "David Biddulph" wrote: Exactly what did your formula look like? -- David Biddulph "Liana" wrote in message ... I am trying to sum totals from 19 pages, I have entered 2 dummy sheets at the beginning & end & now want monthly totals. each worksheet has monthly totals but I need the overall month for the whole team, I tried sum(first:last cell number) but the answer is zero - what am I missing ? |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am attempting to create a summary sheet
For each cell that you are trying to add, if it has a number in it then
=ISNUMBER(cell_ref) should return TRUE, and =ISTEXT(cell_ref) should return FALSE. If the results are the other way round (TRUE for ISTEXT and false for ISNUMBER), then you may well have problem using those cells in your SUM formula. One confusing feature of Excel is that if you have a cell containg =" 1 " or a cell containing '100, those text values will be omitted by the SUM formula, but if you had =cell_ref_1+cell_ref_2+cell_ref_3, then the numbers (1 and 100 respectively) hidden in the text are included in the addition. -- David Biddulph "Liana" wrote in message ... I am now confused - where will the text be that should be numbers etc - how do I check that ? "David Biddulph" wrote: Either you've got a different formula from what you think you've got, or you've got different values in the input cells from what you think you've got (and one possibility in the last category is that you've got text where you think you've got numbers). -- David Biddulph "Liana" wrote in message ... I have managed to get a total for one sum but if I try with the other 2 totals in the same way I still get a zero figure.. "David Biddulph" wrote: You missed out a small but important exclamation mark. It should be =SUM(first:last!B48) -- David Biddulph "Liana" wrote in message ... =SUM(first:lastB48), I also tried sum= () to which I clicked on every persons monthly total cell & hoped to get an answer - still no luck "David Biddulph" wrote: Exactly what did your formula look like? -- David Biddulph "Liana" wrote in message ... I am trying to sum totals from 19 pages, I have entered 2 dummy sheets at the beginning & end & now want monthly totals. each worksheet has monthly totals but I need the overall month for the whole team, I tried sum(first:last cell number) but the answer is zero - what am I missing ? |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time formatt
I am trying to work out a summary of times taken for tasks i have set up
formula of sum(first:last!WA35) which represents everyones time for training in a month however it should be 27hours 20 mins but shows as 99:35:00 how do I convert this to the correct time "Roger Govier" wrote: Hi Liana Don't give up, it will work. If you want to mail me a copy of your workbook, I will take a look. to mail direct send to roger at technology4u dot co dot uk Change the at and dots to the obvious. -- Regards Roger Govier "Liana" wrote in message ... My workbook is laid out exactly that way , I am now beginning to give up just when I thought I was getting the hang of it all !! "Roger Govier" wrote: Hi Liana Your sheet order should be Summary Sheet with your Sum formula First (Blank) Sheet1 .. .. Sheet19 Last (Blank) The Summary sheet can be before First, or after Last, but should not be in between. -- Regards Roger Govier "Liana" wrote in message ... I have managed to get a total for one sum but if I try with the other 2 totals in the same way I still get a zero figure.. "David Biddulph" wrote: You missed out a small but important exclamation mark. It should be =SUM(first:last!B48) -- David Biddulph "Liana" wrote in message ... =SUM(first:lastB48), I also tried sum= () to which I clicked on every persons monthly total cell & hoped to get an answer - still no luck "David Biddulph" wrote: Exactly what did your formula look like? -- David Biddulph "Liana" wrote in message ... I am trying to sum totals from 19 pages, I have entered 2 dummy sheets at the beginning & end & now want monthly totals. each worksheet has monthly totals but I need the overall month for the whole team, I tried sum(first:last cell number) but the answer is zero - what am I missing ? |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am attempting to create a summary sheet
I have a formula on my worksheet which is trying to calculate a weekly
average but the daily average is made up of 2 different percentages =AVERAGE(C43,P37) the trouble is if there is a blank day eg a bank holiday then the average is DIV#0!, I know I need to do an IF sum but how do I do it when the 2 averages are added, I have tried to do first:last(cell no) but if there is a blank day then I get no weekly total Can anyone help ? "Roger Govier" wrote: Hi Liana Don't give up, it will work. If you want to mail me a copy of your workbook, I will take a look. to mail direct send to roger at technology4u dot co dot uk Change the at and dots to the obvious. -- Regards Roger Govier "Liana" wrote in message ... My workbook is laid out exactly that way , I am now beginning to give up just when I thought I was getting the hang of it all !! "Roger Govier" wrote: Hi Liana Your sheet order should be Summary Sheet with your Sum formula First (Blank) Sheet1 .. .. Sheet19 Last (Blank) The Summary sheet can be before First, or after Last, but should not be in between. -- Regards Roger Govier "Liana" wrote in message ... I have managed to get a total for one sum but if I try with the other 2 totals in the same way I still get a zero figure.. "David Biddulph" wrote: You missed out a small but important exclamation mark. It should be =SUM(first:last!B48) -- David Biddulph "Liana" wrote in message ... =SUM(first:lastB48), I also tried sum= () to which I clicked on every persons monthly total cell & hoped to get an answer - still no luck "David Biddulph" wrote: Exactly what did your formula look like? -- David Biddulph "Liana" wrote in message ... I am trying to sum totals from 19 pages, I have entered 2 dummy sheets at the beginning & end & now want monthly totals. each worksheet has monthly totals but I need the overall month for the whole team, I tried sum(first:last cell number) but the answer is zero - what am I missing ? |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am attempting to create a summary sheet
If you want to avoid the #DIV/0! problem, you could try
=IF(COUNT(C43,P37)=2,AVERAGE(C43,P37),"") -- David Biddulph "Liana" wrote in message ... I have a formula on my worksheet which is trying to calculate a weekly average but the daily average is made up of 2 different percentages =AVERAGE(C43,P37) the trouble is if there is a blank day eg a bank holiday then the average is DIV#0!, I know I need to do an IF sum but how do I do it when the 2 averages are added, I have tried to do first:last(cell no) but if there is a blank day then I get no weekly total Can anyone help ? "Roger Govier" wrote: Hi Liana Don't give up, it will work. If you want to mail me a copy of your workbook, I will take a look. to mail direct send to roger at technology4u dot co dot uk Change the at and dots to the obvious. -- Regards Roger Govier "Liana" wrote in message ... My workbook is laid out exactly that way , I am now beginning to give up just when I thought I was getting the hang of it all !! "Roger Govier" wrote: Hi Liana Your sheet order should be Summary Sheet with your Sum formula First (Blank) Sheet1 .. .. Sheet19 Last (Blank) The Summary sheet can be before First, or after Last, but should not be in between. -- Regards Roger Govier "Liana" wrote in message ... I have managed to get a total for one sum but if I try with the other 2 totals in the same way I still get a zero figure.. "David Biddulph" wrote: You missed out a small but important exclamation mark. It should be =SUM(first:last!B48) -- David Biddulph "Liana" wrote in message ... =SUM(first:lastB48), I also tried sum= () to which I clicked on every persons monthly total cell & hoped to get an answer - still no luck "David Biddulph" wrote: Exactly what did your formula look like? -- David Biddulph "Liana" wrote in message ... I am trying to sum totals from 19 pages, I have entered 2 dummy sheets at the beginning & end & now want monthly totals. each worksheet has monthly totals but I need the overall month for the whole team, I tried sum(first:last cell number) but the answer is zero - what am I missing ? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Create summary sheet of last row of info from other sheets | Excel Worksheet Functions | |||
| How can i copy data from a tabbed working sheet to a summary sheet | Excel Discussion (Misc queries) | |||
| Copying Cells From Multiple Worksheets to Create Summary Sheet | Excel Discussion (Misc queries) | |||
| Relative Sheet Reference (Summary Sheet) | Excel Discussion (Misc queries) | |||
| Create summary based on specifc value in a different sheet | Excel Worksheet Functions | |||
Linear Mode
