I've a workbook with 1 project management sheet and several sheets with
testdata. The project management sheet I use for instance to show total
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. Easy
you say....but what I don't know is: how many sheets shall I have, and
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 the
sheet begins with xxx and make a sum of cell A1?

thanks in advance.

david


--
daolb
------------------------------------------------------------------------
daolb's Profile: http://www.excelforum.com/member.php...o&userid=24478
View this thread: http://www.excelforum.com/showthread...hreadid=381509

Try:

=SUM(Sheet1:Sheet4!A1)

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

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=381509

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


--
daolb
------------------------------------------------------------------------
daolb's Profile: http://www.excelforum.com/member.php...o&userid=24478
View this thread: http://www.excelforum.com/showthread...hreadid=381509

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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=381509