Solved: variable formular depending on number of work sheets

[pir81]

Hi,

I am totaly new to VBA. What I want to do is have one work sheet that sums up all the other work sheets (their number is variable)

So in the summed worksheet cell A5 includes all the A5s from the others (once the formular is in place in one cell I can copy paste it across the sheet).

The formular should look like this

if(ISNUMBER(Sheetx!A5),A5,0)+
if(ISNUMBER(Sheety!A5),A5,0)+
if(ISNUMBER(Sheetz!A5),A5,0) etc
Can someone help me?


I tried pasting it together (by the way I want to leave out the first 3 sheets out of the calculation), but am stuck:



Sub Q4_detailed_update()
For i = 1 To Sheets.Count
If Sheets(i).Name = "Sheet1" Or _
Sheets(i).Name = "Sheet2" Or _
Sheets(i).Name = "Sheet3" Or _
Sheets(i).Name = vbNullString Then
Exit
Else
' I assume here should be something like get current string and append new sheet formular, but here I am stuck
Next i
End Sub


Any help is appreciated.
Thanks

[Bob Phillips]

Sub Q4_detailed_update()
Dim tmp As String
Dim i As Long

For i = 1 To Sheets.Count
If Sheets(i).Name = "Sheet1" Or _
Sheets(i).Name = "Sheet2" Or _
Sheets(i).Name = "Sheet3" Or _
Sheets(i).Name = vbNullString Then
Else

tmp = tmp & "+IF(ISNUMBER('" & Worksheets(i).Name & "'!A5),'" & Worksheets(i).Name & "'!A5,0)" & Chr(10)
End If
Next i

ActiveSheet.Range("A5").Formula = "=" & Right(tmp, Len(tmp) - 1)
End Sub

[pir81]

xld,
thank you so much, it works a dream, I am so happy :rotlaugh:

[mikerickson]

The worksheet formula
=SUM(firstSheet:lastSheet!A1)

will return the sum of A1 in firstSheet, lastSheet and all sheets in between.

[pir81]

mikerickson,
that is a great way too and I might have considered it before. But after some time using my formulas I realized that people were moving the spreadsheet around, thus a fixed sheet inclusion is the only way.

Thank you, as it is great to know this anyhow.

[mikerickson]

One trick is to (very?)hide firstSheet and lastSheet so that the user has to go to great lengths to move sheets from in between them.