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?

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?

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?

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?

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?

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?