Remember Me?

#1
October 28th 04, 02:09 AM
 Not2Bright Posts: n/a
SUM function over infinite number of sheets?

Hi Folks

I've currently got a workbook with 5 spreadsheets. The first sheet
acts as a summary sheet and totals up the values in cell A1 in each of
the remaining four sheets. Here's my formula that sits on sheet 1:

=SUM('Sheet2'!A1+'Sheet3'!A1+'Sheet4'!A1+'Sheet5'! A1)

The thing is, I'm constantly adding new sheets to the workbook (e.g.
Sheet6, Sheet7, etc, etc) and, at the moment, I have to manually add
these to my formula on the summary sheet to get the correct totals
e.g.

=SUM('Sheet2'!A1+'Sheet3'!A1+'Sheet4'!A1+'Sheet5'! A1*+'Sheet6'!A1+'Sheet7'!A1*)

Is there anyway in Excel using either a built-in function or VB that
could allow me to do something like this:

=SUM('*Any sheet other than this one*'!A1)

Any help much appreciated. Cheers!

--
Not2Bright
------------------------------------------------------------------------
Not2Bright's Profile: http://www.excelforum.com/member.php...o&userid=15802

#2
October 28th 04, 02:35 AM
 Dave Peterson Posts: n/a

Insert a worksheet to the left and right of your worksheets that get added:

Call the one to the left: Start
call the one to the right: End

then use a formula like:

=sum('start:end'!a1)

in your summary sheet. (keep that summary sheet to the far right or far
left--not between these to sheets.

If you insert a new sheet, just add it between these two.

If you want to see what happens if you got rid of a sheet, just move it from
between the sheets.

Not2Bright wrote:

Hi Folks

I've currently got a workbook with 5 spreadsheets. The first sheet
acts as a summary sheet and totals up the values in cell A1 in each of
the remaining four sheets. Here's my formula that sits on sheet 1:

=SUM('Sheet2'!A1+'Sheet3'!A1+'Sheet4'!A1+'Sheet5'! A1)

The thing is, I'm constantly adding new sheets to the workbook (e.g.
Sheet6, Sheet7, etc, etc) and, at the moment, I have to manually add
these to my formula on the summary sheet to get the correct totals
e.g.

=SUM('Sheet2'!A1+'Sheet3'!A1+'Sheet4'!A1+'Sheet5'! A1*+'Sheet6'!A1+'Sheet7'!A1*)

Is there anyway in Excel using either a built-in function or VB that
could allow me to do something like this:

=SUM('*Any sheet other than this one*'!A1)

Any help much appreciated. Cheers!

--
Not2Bright
------------------------------------------------------------------------
Not2Bright's Profile: http://www.excelforum.com/member.php...o&userid=15802

--

Dave Peterson

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post hon123456 Excel Discussion (Misc queries) 2 January 19th 05 02:41 PM ryanjh79 Excel Discussion (Misc queries) 8 December 23rd 04 10:01 PM Martini25 Excel Discussion (Misc queries) 1 December 21st 04 11:39 AM Ted Dalton Excel Discussion (Misc queries) 1 December 14th 04 03:15 PM darebo Excel Discussion (Misc queries) 2 December 6th 04 05:01 PM

All times are GMT +1. The time now is 08:01 AM.