PDA

View Full Version : Sum cell values on several worksheets



vinny2984
10-17-2008, 05:19 AM
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
10-17-2008, 05:57 AM
=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
10-17-2008, 06:00 AM
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
10-17-2008, 06:12 AM
No.

vinny2984
10-17-2008, 08:22 AM
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
10-17-2008, 10:30 AM
For Each cell In Selection

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

vinny2984
10-17-2008, 10:43 AM
That brings up a syntax error for the formula line

Bob Phillips
10-17-2008, 11:17 AM
Correction



Dim cell As Range

For Each cell In Selection

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

vinny2984
10-17-2008, 11:56 AM
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
10-17-2008, 12:12 PM
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
10-17-2008, 01:08 PM
brillaint, wel done, many thanks for your help and time

mdmackillop
10-17-2008, 03:17 PM
:clap: :clap: :clap:

vinny2984
10-18-2008, 03:10 AM
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
10-18-2008, 03:28 AM
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
10-18-2008, 03:35 AM
at last,this works
cell.Formula = "=SumBySheet(4," & count & ",""" & cell.Address(False, False) & """)"
thanks for all your help
Vinny