How to Sum the same cell across all sheets from 1 to current

[DownUnderFar]

I created a workbook with 10 tabs, named 01, 02, 03 and 04. First I want to sum Cells A1-A4 and put them in B1 to Sheet 01. Second, I want to Sum A1-A4 of both Sheets 01 and 02 and place that in Cell B1 of Sheet 02. This I want to take the Sum of cells A1-A4 on sheets 01, 02, and 03 and place it in tab B1 of Sheet 03

I created the first sheet and place the formula in Cell B1 =Sum('01'!A1:A4)
I then copied the sheet 3 times and named them 02, 03, 04
Then in Sheet 02 I used the replace tool to replace '01'! with '01:02'! However excel keeps wanting to reference an external document or workbook.

My intention was to go to Sheet 03 and replace '01'! with '01:03'! but I didn't get that far.

Why do I keep getting the request for the external workbook?

A different solution is if I were to change the sheet names to 1, 2, 3, and 4. Then in cell C1 I could enter type the sheet number. If I could then sum the values of cells A1-A4 of sheets 1 - [Value of cell C1] that would work if anyone knows how to write that.

Finally, I tried using the sheet[number], the master sheet# that you find in VBA. I made sure the the sheet named 01 was sheet1 and the sheet named 02 was sheet2, etc. When I try to sum the range A1:A4 from 'sheet1:sheetX'! it says it can not find the sheet and wants to open an external link.

I tried to create a mini-sheet but the symptoms don't reproduce the same.

 

[jolivanes]

Is this of any help?

make the code shorter [SOLVED]

Hello, Is there a way to make it the code shorter instead of typing sheet1, sheet2, sheet3, etc. I have about Sheet1 thru Sheet7. =SUM(Sheet1!G8+Sheet2+Sheet2+Sheet3+Sheet4+Sheet5+sheet6+Sheet7!G8) THANK YOU

www.excelforum.com

 

[DownUnderFar]

It kind of isn't. The 'begin:end' concept is really the same as me doing '01:10' for all sheets. WhatI want is for sheet 01 to calculate all of the same cells in sheet 01, sheet 2 to be '01:02', sheet 03 to be '01:03', sheet4 to be '01:04', etc. I was hoping to be able to replace the second number with either the sheet name or a reference a cell and I will type the sheet name in that cell.

But more importantly, why do I keep getting the prompt about reference to an external link? I never added an external link.

 

[jolivanes]

Correct me if I am wrong but if you have the Sum of Range("A1:A4") in Range("B1") then in Sheet2 you could Sum Range("A1:A4") of Sheet2 + Sheet1.Range("B1") in Range("B1").
Enter the formulas with a macro.

Sub Maybe()
Dim i As Long
Sheets("01").Range("B1").Formula = "=SUM(A1:A4)"
    For i = 2 To ThisWorkbook.Worksheets.Count
        Sheets(i).Range("B1").Formula = "=SUM(A1:A4)+'" & Sheets(Sheets(i).Index - 1).Name & "'!B1"
    Next i
End Sub