Automated =SUM() sequence?

Microsoft Corporation download - microso...
September 16, 2010 at 14:49:05
Specs: Windows Vista
I have a spreadsheet with 4 sheets (while testing) that will have around 300 sheets eventually. I am adding up the value of the SAME CELL but within several other sheets (eventually all of those 300 I mentioned earlier).
I'm using =SUM for far looks like this:
as you can see the current names I've given the sheets are 001,002,003,004 - what I want to avoid is to have to manually enter " '###'!C15, " every time I create a sheet.

Any ideas? I don't think the solution must be within Excel, I just need something that creates this sequence for me, maybe another text editor..?

See More: Automated =SUM() sequence?

September 16, 2010 at 14:57:54
Somebody just told me that what I really need is to add something to the formula so that it automatically reads from the same cell on ANY new sheet created....STILL no answer to what I need.

Report •

September 16, 2010 at 16:12:06
One way I can think of is to create a new sheet, call it Totals

In Cell A1 enter the TEXT number 001, it must be formatted as text else you lose the leading zeros.

In Cell A2 enter the TEXT number 002.

Hightlight cells A1 & A2 and drag down 300 rows.

You should now have column A filled with the TEXT numbers 001 - 300

In cell B1 enter the text: C15

In cell C1 enter the formula: =INDIRECT(A1&"!"&$B$1)

drag the formula down 300 rows.

You can now either do a simple =SUM(c1:c300) on row 301 or you can do a running total using column D


Report •

September 16, 2010 at 16:20:48
If you don't want to see any error messages about invalid cell references, then try this formula:



Report •

Related Solutions

September 16, 2010 at 16:52:29
Paste this code into the ThisWorkbook module of the VBA editor.

Each time you add a sheet (or sheets) it will fire and build a string based on the sheet names and then place the formula in Sheet1!A1:

Option Explicit
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim sht As Integer
Dim formTmp, formFinal As String
'Loop through sheets
  For sht = 1 To Sheets.Count
'Build Temporary string for formula
   formTmp = formTmp & "'" & Sheets(sht).Name & "'!C15,"
'Strip off last comma and add additional characters
'to build Final formula string
   formFinal = "=sum(" & Left(formTmp, Len(formTmp) - 1) & ")"
'Place formula in Sheet1!A1
   Sheets(1).Range("A1").Formula = formFinal
End Sub

Report •

September 16, 2010 at 17:35:23
Thanks a lot, this works very nicely!!

Report •

September 16, 2010 at 17:38:52
Derby, I do have a question, how can I avoid counting the first and last sheets, or to avoid A sheet in particular, let's call these two sheets TEMPLATE and RESULTS

Report •

September 16, 2010 at 18:43:19
To avoid the first and last sheets, just change the loop parameters:

For sht = 2 To Sheets.Count - 1

To avoid specific sheets, check the name of each sheet and jump over the code that builds the string:

'Loop through sheets
  For sht = 1 To Sheets.Count
'Check sheet name, skip specific names
     If Sheets(sht).Name = "TEMPLATE" Or _
        Sheets(sht).Name = "RESULTS" Then _
        GoTo SkipSheet
'Build Temporary string for formula
    formTmp = formTmp & "'" & Sheets(sht).Name & "'!C15,"

Report •

September 28, 2010 at 14:54:57
Derby, would you mind looking at the file I have?..for some reason the code is not firing up every time I add a new sheet.

see here:

look at the JPG for an explanation of what I want to do.

Thanks A LOOOOOT!!!

Report •

Ask Question