Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Not2Bright
 
Posts: n/a
Default 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
View this thread: http://www.excelforum.com/showthread...hreadid=273042

  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
View this thread: http://www.excelforum.com/showthread...hreadid=273042


--

Dave Peterson

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
using countif function to add only a half of a number ryanjh79 Excel Discussion (Misc queries) 9 April 26th 23 03:42 AM
Seed numbers for random number generation, uniform distribution darebo Excel Discussion (Misc queries) 3 April 21st 23 09:02 PM
The number change from 1350 to 13.5 and Enter does not function properly hon123456 Excel Discussion (Misc queries) 2 January 19th 05 03:41 PM
Paste a function as a fixed number Martini25 Excel Discussion (Misc queries) 1 December 21st 04 12:39 PM
#VALUE in cell but pop up function box show right number Ted Dalton Excel Discussion (Misc queries) 1 December 14th 04 04:15 PM


All times are GMT +1. The time now is 12:28 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"