Adding Cell values across sheets to current

[EatingSteak]

I am trying to create a running "month to date" value for certain data. The data for each day is in a different sheet, in the same format

So, let's say in cell A3 I have "number of apples sold" and I have Sheet1 for the 1st of the month, Sheet 2 for the 2nd, etc

If I wanted the total number of apples sold, I could put:

=SUM('Sheet1:Sheet30'!A3)

But what if I wanted a "running tab" for Month to Date? I would need something like "Sum of all A3 values from (Sheet1) to (currentsheet). Is there any way I can do this?

[Blake 7]

You can certainly do

=Sheet1!$A$1+Sheet2!$A$1+Sheet3!$A$1 or =SUMPRODUCT(Sheet1!A1)+(Sheet2!A1)+(Sheet3!A1)

but sum requires the values to be in the same range.

[EatingSteak]

That is not exactly what I am looking for. I want to paste a formula into every worksheet so that it will total a cell (say A3) from every sheet starting from Sheet1 and going up to the current sheet.
So on Sheet4 it would be =SUM('Sheet1:Sheet4'!A3) and on Sheet13 it would be =SUM('Sheet1:Sheet13'!A3).
I am asking if there is a way to put a 'variable' sheet number so I could have the current sheet instead of typing that Sheet number into every page. Thank you for your reply though.

[DonkeyOte]

Unfortunatley the only "shorthand" for entering the 3D aggregation is to use * and that will include all sheets except current, eg:

=SUM('*'!A1)

will translate on entry - but will not give you what you want.

Moreover you can not create 3D references with the likes of INDIRECT - you would need a Volatile SUMPRODUCT and a physical listing of the relevant sheets for inclusion.

Given the above... if:

a) the "SUM" cell is the same on each sheet

b) consistent incrementing sheet naming convention

it might make more sense (for sake of simplicity) to reference the prior sheet aggregation cell to which you would add the current value.

You can use CELL function (etc) to determine the appropriate sheet reference for prior sheet aggregate meaning you can copy the formula to all sheets
(you will want an error handler for the aggregate if you opt to use the same formula on sheet1 of course)