"Myrna Larson" wrote...
I don't think you're doing it the long way. Given your layout, you are
doing the only way. If I understand correctly, the formula on sheet 2 is
something like

=SUM(Sheet1:Sheet1!A1)

on Sheet5 it's =SUM(Sheet1:Sheet5!A1)

and on Sheet 31 it's =SUM(Sheet1:Sheet31!A1)

I second Dave Peterson's suggestion that you should put all of the data
on one sheet, in database format, with a column to indicate the date. It
will make your life much simpler in the end <g.
....

Especially since Microsoft has deomonstrated no intention of ever making
Excel a true 3D spreadsheet. There's always OpenOffice Calc, which has
relative worksheets in its 3D references, or 123 or Quattro Pro.

But if one is stuck having to use Excel, there's trickery. Assuming the
workbook in question has been saved, create an ordered list of worksheet
names and name it something WSList. Then create the defined name WSName
referring to the formula

=MID(CELL("Filename",INDIRECT("A1")),
FIND("]",CELL("Filename",INDIRECT("A1")))+1,32)

and use the formula

=SUMPRODUCT(SUMIF(INDIRECT("'"&OFFSET(WSList,0,0,
MATCH(WSName,WSList,0),1)&"'!A1"),"<0"))

Then again, if there's a simple pattern to the worksheet names, and the
cumulative cell is always, say, X99, which adds the value of cell A1 from
the current worksheet to the total from the previous worksheet, it'd be
easier to use something like

=INDIRECT("Sheet"&(SUBSTITUTE(WSName,"Sheet","")-1)&"!X99")+A1