named range in sum formula (indirect, offset, worksheet name)

[Hans]

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),FIND("]",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

 

[Bob Phillips]

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]

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),FIND("]",CELL("filename",A1))+1,255)&"1"))

Thanks for any help!

Hans

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)

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),FIND("]",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

 

[Bob Phillips]

Select the appropriate worksheet

Go to menu Insert>Name>Define...

In the Names box insert

this_sheet_name!Range1

and in the RefersTo box insert

=OFFSET(...

Then onto the next sheet and repaet the exercise, and so on.

Then on each sheet you just use

=SUM(Range1)

which you can copy from sheet to sheet.

A bit better than INDIRECTs.

--
---
HTH

Bob


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

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),FIND("]",CELL("filename",A1))+1,255)&"1"))

Thanks for any help!

Hans

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)

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),FIND("]",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

 

[Hans]

sense and simplicity! thx for the help.

Hans

Select the appropriate worksheet

Go to menu Insert>Name>Define...

In the Names box insert

this_sheet_name!Range1

and in the RefersTo box insert

=OFFSET(...

Then onto the next sheet and repaet the exercise, and so on.

Then on each sheet you just use

=SUM(Range1)

which you can copy from sheet to sheet.

A bit better than INDIRECTs.

--
---
HTH

Bob


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

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),FIND("]",CELL("filename",A1))+1,255)&"1"))

Thanks for any help!

Hans

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)



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),FIND("]",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