Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sheet to Sheet Totals
I have 31 worksheets for everyday of the month. In each sheet I'm summing up
several different figures. I would like to insert another worksheet and maintain a running total from each sheet. Can you help? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sheet to Sheet Totals
Hi
On your Summary Sheet =SUM(Sheet1:Sheet31!A1) Since data will presumably not have been entered into dates beyond say 27th, the Sum will effectively only be from Sheet1 to Sheet27 and therefore the running total. Alternatively, create 2 extra sheets called First and Last and make the formula =Sum(First:Last!A1) Drag First and Last to a position to "sandwich any of the range 1 to 31, and the result will be the total for just those sheets. -- Regards Roger Govier "Skydiver" wrote in message ... I have 31 worksheets for everyday of the month. In each sheet I'm summing up several different figures. I would like to insert another worksheet and maintain a running total from each sheet. Can you help? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sheet to Sheet Totals
Roger,
Hello and thank you for your reply. Data will be entered in all 31 sheets...is it possible to have one summary sheet with a running total for each day? Please excuse my stupidity...hope you can help. Thanks again. John "Roger Govier" wrote: Hi On your Summary Sheet =SUM(Sheet1:Sheet31!A1) Since data will presumably not have been entered into dates beyond say 27th, the Sum will effectively only be from Sheet1 to Sheet27 and therefore the running total. Alternatively, create 2 extra sheets called First and Last and make the formula =Sum(First:Last!A1) Drag First and Last to a position to "sandwich any of the range 1 to 31, and the result will be the total for just those sheets. -- Regards Roger Govier "Skydiver" wrote in message ... I have 31 worksheets for everyday of the month. In each sheet I'm summing up several different figures. I would like to insert another worksheet and maintain a running total from each sheet. Can you help? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sheet to Sheet Totals
Hi John
Please excuse my stupidity...hope you can help No question of you being stupid at all, there is probably a misunderstanding in my interpretation of your requirement. If you are saying you want to have a sheet with A1:A31 containing Sheet1, Sheet2, ... Sheet31 or whatever you have named your sheets, and that on each row you want to pick out values from certain cells on those sheets, then in B1 enter =INDIRECT("'"&$A1&"'!B1") As you copy down the sheet, this would pick up the value from Cell B1 from each of the 31 sheets Change the value of B1 to any other cell that you are trying to extract. Is this what you mean? If not post back with some more detail. We'll get there in the end. I would be bound to ask the question, as to why it is necessary to use 31 sheets to enter the data. Could you not do it all on the same sheet, with an additional column for the day number? That way, using Filters and Subtotal, it would be very easy to pick up any data required. Just a thought. -- Regards Roger Govier "Skydiver" wrote in message ... Roger, Hello and thank you for your reply. Data will be entered in all 31 sheets...is it possible to have one summary sheet with a running total for each day? Please excuse my stupidity...hope you can help. Thanks again. John "Roger Govier" wrote: Hi On your Summary Sheet =SUM(Sheet1:Sheet31!A1) Since data will presumably not have been entered into dates beyond say 27th, the Sum will effectively only be from Sheet1 to Sheet27 and therefore the running total. Alternatively, create 2 extra sheets called First and Last and make the formula =Sum(First:Last!A1) Drag First and Last to a position to "sandwich any of the range 1 to 31, and the result will be the total for just those sheets. -- Regards Roger Govier "Skydiver" wrote in message ... I have 31 worksheets for everyday of the month. In each sheet I'm summing up several different figures. I would like to insert another worksheet and maintain a running total from each sheet. Can you help? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sheet to Sheet Totals
Roger,
Thanks for your patience. Your suggestion regarding "all in one sheet" thing sounds like a better idea. Let me play with that. The reason for the 31 worksheets is only because the file is named December '06 and the user can pick and choose which day to go into & review the numbers for a variety of fields. I'll try your idea...we'll see what happens. Thanks Roger. John "Roger Govier" wrote: Hi John Please excuse my stupidity...hope you can help No question of you being stupid at all, there is probably a misunderstanding in my interpretation of your requirement. If you are saying you want to have a sheet with A1:A31 containing Sheet1, Sheet2, ... Sheet31 or whatever you have named your sheets, and that on each row you want to pick out values from certain cells on those sheets, then in B1 enter =INDIRECT("'"&$A1&"'!B1") As you copy down the sheet, this would pick up the value from Cell B1 from each of the 31 sheets Change the value of B1 to any other cell that you are trying to extract. Is this what you mean? If not post back with some more detail. We'll get there in the end. I would be bound to ask the question, as to why it is necessary to use 31 sheets to enter the data. Could you not do it all on the same sheet, with an additional column for the day number? That way, using Filters and Subtotal, it would be very easy to pick up any data required. Just a thought. -- Regards Roger Govier "Skydiver" wrote in message ... Roger, Hello and thank you for your reply. Data will be entered in all 31 sheets...is it possible to have one summary sheet with a running total for each day? Please excuse my stupidity...hope you can help. Thanks again. John "Roger Govier" wrote: Hi On your Summary Sheet =SUM(Sheet1:Sheet31!A1) Since data will presumably not have been entered into dates beyond say 27th, the Sum will effectively only be from Sheet1 to Sheet27 and therefore the running total. Alternatively, create 2 extra sheets called First and Last and make the formula =Sum(First:Last!A1) Drag First and Last to a position to "sandwich any of the range 1 to 31, and the result will be the total for just those sheets. -- Regards Roger Govier "Skydiver" wrote in message ... I have 31 worksheets for everyday of the month. In each sheet I'm summing up several different figures. I would like to insert another worksheet and maintain a running total from each sheet. Can you help? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sheet to Sheet Totals
Hi John
I'm glad your going to try holding all data on one sheet. It has so many advantages for all types of reporting, it will be well worth your while experimenting. Just a couple of very quick tips. Having a second column of dates, in a different date format can be useful. Assuming row 1 is your header row, and supposing you add a column for dates at column M, then in column N add the formula = M2 for column N, FormatCellsNumberCustom mmm-yy and it will display just the month and year name, Dec-06 for example. Then, with DataFilterAutofilter applied, use the dropdown on column N and 2 clicks will select the month you want to view. Inserting a row or two above you header, pushing it down to row 3, allows some space to use =SUBTOTAL(9,A3:A1000) which will Sum all the visible rows in column A after thee filter has been applied. Placing the cursor in cell A4 and choosing WindowsFreeze panes, will keep the headings and subtotals visible at all times as you scroll down the list. If you have any further problems, post back. -- Regards Roger Govier "Skydiver" wrote in message ... Roger, Thanks for your patience. Your suggestion regarding "all in one sheet" thing sounds like a better idea. Let me play with that. The reason for the 31 worksheets is only because the file is named December '06 and the user can pick and choose which day to go into & review the numbers for a variety of fields. I'll try your idea...we'll see what happens. Thanks Roger. John "Roger Govier" wrote: Hi John Please excuse my stupidity...hope you can help No question of you being stupid at all, there is probably a misunderstanding in my interpretation of your requirement. If you are saying you want to have a sheet with A1:A31 containing Sheet1, Sheet2, ... Sheet31 or whatever you have named your sheets, and that on each row you want to pick out values from certain cells on those sheets, then in B1 enter =INDIRECT("'"&$A1&"'!B1") As you copy down the sheet, this would pick up the value from Cell B1 from each of the 31 sheets Change the value of B1 to any other cell that you are trying to extract. Is this what you mean? If not post back with some more detail. We'll get there in the end. I would be bound to ask the question, as to why it is necessary to use 31 sheets to enter the data. Could you not do it all on the same sheet, with an additional column for the day number? That way, using Filters and Subtotal, it would be very easy to pick up any data required. Just a thought. -- Regards Roger Govier "Skydiver" wrote in message ... Roger, Hello and thank you for your reply. Data will be entered in all 31 sheets...is it possible to have one summary sheet with a running total for each day? Please excuse my stupidity...hope you can help. Thanks again. John "Roger Govier" wrote: Hi On your Summary Sheet =SUM(Sheet1:Sheet31!A1) Since data will presumably not have been entered into dates beyond say 27th, the Sum will effectively only be from Sheet1 to Sheet27 and therefore the running total. Alternatively, create 2 extra sheets called First and Last and make the formula =Sum(First:Last!A1) Drag First and Last to a position to "sandwich any of the range 1 to 31, and the result will be the total for just those sheets. -- Regards Roger Govier "Skydiver" wrote in message ... I have 31 worksheets for everyday of the month. In each sheet I'm summing up several different figures. I would like to insert another worksheet and maintain a running total from each sheet. Can you help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum up columns in different sheet with error check | Excel Discussion (Misc queries) | |||
Totals sheet - deal with employee names | Excel Worksheet Functions | |||
Referencing cell in another sheet yields null? | Excel Worksheet Functions | |||
How to add totals in one sheet to another sheet | Excel Discussion (Misc queries) | |||
calculate totals of 5 sheets on to 6th sheet. | Excel Worksheet Functions |