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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Hans - 05 Feb 2008 08:46 GMT

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

Reply to this Message

Bob Phillips - 05 Feb 2008 09:11 GMT

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.

Signature

---
HTH

Bob

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

> Hi,
>
[quoted text clipped - 28 lines]
>
> Hans

Reply to this Message

Hans - 05 Feb 2008 11:38 GMT

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?
>
[quoted text clipped - 34 lines]
> >
> > Hans

Reply to this Message

Bob Phillips - 05 Feb 2008 14:04 GMT

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.

Signature

---
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
[quoted text clipped - 92 lines]
>> >
>> > Hans

Reply to this Message

Hans - 05 Feb 2008 14:14 GMT

sense and simplicity! thx for the help.

Hans

> Select the appropriate worksheet
>
[quoted text clipped - 114 lines]
> >> >
> >> > Hans

Reply to this Message