Sheet to Sheet Totals

[Guest]

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?

 

[Roger Govier]

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.

 

[Guest]

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]

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.

 

[Guest]

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]

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, Format>Cells>Number>Custom> mmm-yy and it will display
just the month and year name, Dec-06 for example.
Then, with Data>Filter>Autofilter 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 Windows>Freeze 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.