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


H

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
 
Ad

Advertisements

B

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)
 
H

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

Bob Phillips said:
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 said:
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
 
B

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)



Hans said:
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

Bob Phillips said:
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 said:
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
 
Ad

Advertisements

H

Hans

sense and simplicity! thx for the help.

Hans

Bob Phillips said:
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)



Hans said:
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

Bob Phillips said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top