Sum cell values on several worksheets

[vinny2984]

I'm having problems to find the right formula to add the cell value of Cell N19 on sheet position 4 through to 80. Some of the cells will have text and some numbers and some blank. I want the result to be just the sum of the numbers and ignoe the cells with anything or nothing else.

I was told to try =sum('4:80'!N19) but that returns a #Ref! error.
I've played with all differnet variations but still no luck.

I can't use the sheet names it needs to be by position. Also will the sum function work with 80 sheets?
or
Is there a way of sum all the sheets, but exclude the ones i don't want by name?

can anyone help me out

thanks.

[Bob Phillips]

=sumbysheet(4,80,"N19")

and



Function SumBySheet(startsheet, endsheet, cellref)

Application.Volatile
SumBySheet = ActiveSheet.Evaluate("SUM('" & _
Worksheets(startsheet).Name & ":" & _
Worksheets(endsheet).Name & "'!" & cellref & ")")
End Function

[vinny2984]

thanks for that, i'll give it a try.
Do you think it is possible to do a formula to do this with sheet positions rather than names?
something like =sum(sheet(1):sheet(80), N19) i've tried many variations of this. is it impossible.

[Bob Phillips]

No.

[vinny2984]

xld
thanks for the code, it works perfectly.
Do you have an ideas,how i can do a sub that will enter that formula in a range of cells taking on the cell address as the cellref?

I was hoping that a formula could do this, so i could enter it into my sub to put it in many cells, refernced to the cells its in. As thats not possible, i ned to find a way to use your function to do this.
any thoughts are well appreciated.

[Bob Phillips]

For Each cell In Selection

cell.Formula = "=SUmBySheet(4,80," & cell.address(False,False), & ")")
Next cell

[vinny2984]

That brings up a syntax error for the formula line

[Bob Phillips]

Correction



Dim cell As Range

For Each cell In Selection

cell.Formula = "=SumBySheet(4,80," & cell.Address(False, False) & ")"
Next cell

[vinny2984]

this is really close now.
the last formula doesnt work because when the sub puts it into the cells, it looses the "" around the cell address. can it be changed to keep them?

[Bob Phillips]

I'l get it eventually



Dim cell As Range

For Each cell In Selection

cell.Formula = "=SumBySheet(4,80,""" & cell.Address(False, False) & """)"
Next cell

[vinny2984]

brillaint, wel done, many thanks for your help and time

[mdmackillop]

:clap: :clap: :clap:

[vinny2984]

XLD, many thanks for what you've done for me, but if your up for it i have on more adjustment whcih i think you can acheive. The code is below for the function and the sub i'm using it in.
Can the endsheet value be changed to worksheets.count value? this way it will automatically calculate the formulas based on the number of sheets rather than manually counting them and adding that count to the formula.
The change i made below (substituting endsheet value for count obviuosly was no good but you can see what i'm trying to achieve
many thanks.

Function SumBySheet(startsheet, endsheet, cellref)

Application.Volatile
SumBySheet = ActiveSheet.Evaluate("SUM('" & _
Worksheets(startsheet).Name & ":" & _
Worksheets(endsheet).Name & "'!" & cellref & ")")
End Function


Sub Pumpsheet_formula_setup()
Application.ScreenUpdating = False
Dim incr As Integer, admin As Range, admin1 As Range, cell As Range, count As Integer
count = Worksheets.count
Set admin = Sheets("Pump").Range("B3:AF4")
For incr = 1 To 49 Step 4
Set admin1 = admin.Offset(incr - 1, 0)
For Each cell In admin1
If IsDate(cell.Offset(-2, 0).Value) Then
cell.Formula = "=SumBySheet(4,count,""" & cell.Address(False, False) & """)"
cell.HorizontalAlignment = xlCenter
cell.VerticalAlignment = xlCenter
End If
Next cell
Next incr
Application.ScreenUpdating = True
End Sub

[vinny2984]

changing the code to this
cell.Formula = "=SumBySheet(4,""" & count & """,""" & cell.Address(False, False) & """)"

gives a formula that doesn't work of
=SumBySheet(4,"6","M24")
but its getting close, if the "" around 6 can be lost, it will be perfect

[vinny2984]

at last,this works
cell.Formula = "=SumBySheet(4," & count & ",""" & cell.Address(False, False) & """)"
thanks for all your help
Vinny