Another data across multiple worksheets question (ugh..)

[Rasman007]

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..

 

[Martin Fishlock]

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.