Hi Bob, I'm not following you:
- how do I create local worksheet names? The only way I know to define names
is for the entire workbook (inser\name\define)
- do you mean the input for the range should be in the form
sheet1!offset(...)? This does not seem to work.

I added some more details about the situation:

Sheet Test1
column A column D
1 02:30
1 03:30
1 04:30
1 05:30
Cl 06:30
Cl 07:30
Cl 08:30
VP 09:30
VP 10:30
VP 11:30
VP 12:30
VP 13:30
VP 14:30

Sheet Test2
column A column D
1 02:30
1 05:30
Cl 06:30
Cl 07:30
Cl 08:30
Cl 09:30
Cl 10:30
VP 11:30
VP 12:30
VP 13:30
VP 14:30

I use the following formula's to define the ranges:
Range1_Test1=OFFSET(Test1!$A$1,MATCH("1*",Test1!$B $1:$B$500,0)-1,4,(MATCH("CL*",Test1!$B$1:$B$500,0)-MATCH("1*",Test1!$B$1:$B$500,0)),1)

Range1_Test2=OFFSET(Test2!$A$1,MATCH("1*",Test2!$B $1:$B$500,0)-1,4,(MATCH("CL*",Test2!$B$1:$B$500,0)-MATCH("1*",Test2!$B$1:$B$500,0)),1)

And for the sumation in each sheet I would like to use something like this:
SUM(INDIRECT("Range1_"&MID(CELL("filename",A1),FIN D("]",CELL("filename",A1))+1,255)&"1"))

Thanks for any help!

Hans

"Bob Phillips" wrote:

Why not use local worksheet names?

In each sheet create the same name Range1, and define it as you do but
precede it with the sheet name, Sheet1!formula, Sheet2!formula etc, then it
is just =SUM(Range1) on each sheet.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Hans" wrote in message
...
Hi,

I'm facing the following situation:
- I've got named ranges using an offset function f.ex.
Range1_Sheet1=offset(...), Range1_Sheet2=offset(...),etc pointing to a
range
of numbers. (The size and position of these range depend on the content
of
values in another column).
- My worksheet names are part of the names of the ranges f.ex. Sheet1,
Sheet2,...
- I'm using a sum function to count the total value of the numbers in each
of these ranges. So far no problem. But...
- The point is that when I copy a sheet and change it's name, I would like
the function to still work i.e sum the values of the range (I still have
to
define this separately) on the new sheet. So looking into this forum I
found
the mid(cell(...)) trick to get the worksheet name. This combined with
sum(indirect()) gives:
=SUM(INDIRECT("Range1_"&MID(CELL("filename",A1),FI ND("]",CELL("filename",A1))+1,255)))

But this does not work.

I think it is linked to the offset function in the range names. In
debugging I discovered that it works when defining an range name without
using offset. Is this a limitation of excel or am I overlooking
something?

Thanks,

Hans