Home 
Search 
Today's Posts 
#1




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




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 leftnot 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 builtin 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 
Display Modes  


Similar Threads  
Thread  Forum  
The number change from 1350 to 13.5 and Enter does not function properly  Excel Discussion (Misc queries)  
using countif function to add only a half of a number  Excel Discussion (Misc queries)  
Paste a function as a fixed number  Excel Discussion (Misc queries)  
#VALUE in cell but pop up function box show right number  Excel Discussion (Misc queries)  
Seed numbers for random number generation, uniform distribution  Excel Discussion (Misc queries) 