Another data across multiple worksheets question (ugh..)
 

Aright, here's my problem that I've already been working an hour on:

End Goal = being able to see current total of all sales on every
worksheet in a single cell.

I have 4 worksheets in my workbook - Jan-Mar'07, Apr-June'07,
July-Sept.'07, Oct.-Nov'07

For example, in the Jan-Mar'07 worksheet, I have a January column, Feb.
column, and March column.
Each month has a tally at the bottom that adds in everyday the new
numbers. Then, to the right, I have a separate cell that adds all the
months together =SUM(B36+E36+H36) , this is in cell K35.

Now, when I click on the second worksheet Apr-June'07 everything is the
exact same, with different numbers each day and months of course. K35
is the total of 3 months in this worksheet as well. And so on for the
following two worksheets.

So, in a nutshell, I guess I'm asking how do I take the values of K35
on every sheet and put it in a new cell that gives me a running tally
for the entire year (so I don't have to keep adding up the K35 cells
every darn time I want to know what the yearly total is - most annoying
at the end of the year when all 4 sheets are full)

I thought it was =SUM(Sheet1:Sheet2:Sheet3:Sheet4!K35) but I keep
getting an 'error in formula' message.

Please help..

Another data across multiple worksheets question (ugh..)
 

Just specify the first and last sheets. All sheets in between are
automatically included.
=SUM(Sheet1:Sheet4!K35)

Alternatively, set up two empty sheets named "First" and "Last" and place
them at either end of the sheets you want added. Then you can move sheets in
and out (from between these two sheets) to include/exclude them from your
total.
=SUM(First:Last!K35)



"Rasman007" wrote:

Aright, here's my problem that I've already been working an hour on:

End Goal = being able to see current total of all sales on every
worksheet in a single cell.

I have 4 worksheets in my workbook - Jan-Mar'07, Apr-June'07,
July-Sept.'07, Oct.-Nov'07

For example, in the Jan-Mar'07 worksheet, I have a January column, Feb.
column, and March column.
Each month has a tally at the bottom that adds in everyday the new
numbers. Then, to the right, I have a separate cell that adds all the
months together =SUM(B36+E36+H36) , this is in cell K35.

Now, when I click on the second worksheet Apr-June'07 everything is the
exact same, with different numbers each day and months of course. K35
is the total of 3 months in this worksheet as well. And so on for the
following two worksheets.

So, in a nutshell, I guess I'm asking how do I take the values of K35
on every sheet and put it in a new cell that gives me a running tally
for the entire year (so I don't have to keep adding up the K35 cells
every darn time I want to know what the yearly total is - most annoying
at the end of the year when all 4 sheets are full)

I thought it was =SUM(Sheet1:Sheet2:Sheet3:Sheet4!K35) but I keep
getting an 'error in formula' message.

Please help..

Another data across multiple worksheets question (ugh..)
 

Couple of solutions for you to consider:

='Jan-Mar''07'!K35 + 'Apr-June''07'!K35 +
'July-Sept.''07'!K35 + 'Oct.-Nov''07'!K35

or

=sum('Jan-Mar''07:Oct.-Nov"'07'!K35)

note the use of 2 ' in the name as you use ' in the name of the sheets.

If you move the sheets around you need to becareful of the links and
sometimes it is easier if you use the sum to but a blank sheet before the
first and after the last one and do the sum inbetween those as in

=sum(dummystart:dummyend!K35)

then you can move the sheets around in between and not cause any problems.


--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Rasman007" wrote:

Aright, here's my problem that I've already been working an hour on:

End Goal = being able to see current total of all sales on every
worksheet in a single cell.

I have 4 worksheets in my workbook - Jan-Mar'07, Apr-June'07,
July-Sept.'07, Oct.-Nov'07

For example, in the Jan-Mar'07 worksheet, I have a January column, Feb.
column, and March column.
Each month has a tally at the bottom that adds in everyday the new
numbers. Then, to the right, I have a separate cell that adds all the
months together =SUM(B36+E36+H36) , this is in cell K35.

Now, when I click on the second worksheet Apr-June'07 everything is the
exact same, with different numbers each day and months of course. K35
is the total of 3 months in this worksheet as well. And so on for the
following two worksheets.

So, in a nutshell, I guess I'm asking how do I take the values of K35
on every sheet and put it in a new cell that gives me a running tally
for the entire year (so I don't have to keep adding up the K35 cells
every darn time I want to know what the yearly total is - most annoying
at the end of the year when all 4 sheets are full)

I thought it was =SUM(Sheet1:Sheet2:Sheet3:Sheet4!K35) but I keep
getting an 'error in formula' message.

Please help..