totals

D

daolb

I've a workbook with 1 project management sheet and several sheets wit
testdata. The project management sheet I use for instance to show tota
figures. For example. I want to make totals for sheet1!A1 + sheet2!A1
sheet3!A1. As you see the position of the cells remanes the same. Eas
you say....but what I don't know is: how many sheets shall I have, an
I don't want to adapt everytime my formulas after adding a sheet
I there a possibility to say take all the sheets where the name of th
sheet begins with xxx and make a sum of cell A1?

thanks in advance.

davi
 
M

mangesh_yadav

Try:

=SUM(Sheet1:Sheet4!A1)

new sheets inserted should be between these 2 sheets in the formula.

Mangesh
 
D

daolb

this solution is not quite what I want.

for instance

sheet 1: scrn_fun_001 A1: 10
sheet 2: Scrn _use_001 A1: 10
sheet 3: scren_fun_002 A1: 10
sheet 4: scrn_use_002 A1: 10


when I take your solution the total sum is 40, and I want only take
into account sheet1 and 3. You could say reorder the sequence of the
sheets.
Not a good solution, because sheets can be added later on, and I don't
want to adapt my formula.

I would like that the system takes all A1 cells where the sheet name
starts with scrn_fun or scrn_use.

david
 
M

mangesh_yadav

Here's a small UDF wihch will help you sum the same cell in all the
sheets.

Function mySum(rng)
For Each Sht In Worksheets
mySum = mySum + Sht.Range(rng.Address)
Next
End Function


A small if statement will help you weed out unwanted sheets, for
instance, lets say you have a summary sheet, and don't want to include
this sheet, then you could modify the above udf to:

Function mySum(rng)
For Each sht In Worksheets
If sht.Name <> "Summary" Then
mySum = mySum + sht.Range(rng.Address)
End If
Next
End Function


The UDF shold go in a standard module in VBE. Press Alt F11 to open
VBE. Insert > Module. And enter the above code.

Usage:
=mysum(A1)
to summ A1 from all sheets.


Mangesh
 
Top