Named Range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Skin - 23 May 2006 12:59 GMT

Hi All, Using Excel 2004 11.2.3
My problem is replicating the following which works on each sheet. I have
three sheets named:- Total_2004, Total_2005, Total_2006. My defined range
name is "extend" Refers to
=OFFSET('Total_2004'!$A$4,0,0,COUNTA(OFFSET("Total_2004"!$M$4,0,0,9999)),25)
When I Press control G and enter extend then ok it goes where I want it to.
When in sheet Total_2004 The defined name "extend" in the refers to box
reads as above, When in sheet Total_2005 The refers to box changes to
=OFFSET('Total_2005'!$A$4,0,0,COUNTA(OFFSET("Total_2005"!$M$4,0,0,9999)),25)
Total_2006 refers to changes the same way. Trying to set up a new named
range to work in the same three sheets the same way but refers to box does
not change and whichever sheet I am in it takes me only to the range of the
sheet that I defined the range in. Hope that made sense. Any Answers as how
to enter the defined name so it works independently in each sheet much
appreciated. Paul

Reply to this Message

CyberTaz - 23 May 2006 13:19 GMT

If I understand your problem, you are finding that range names are *not*
isolated to individual sheets, but are 'global' Workbook references -
although they do refer to a specific group of cells on a specific sheet in
the book there can only be one range by that name.

Try naming your ranges uniquely such as extend_05, extend_06 and you should
be OK.

Signature

HTH |:>)
Bob Jones
[MVP] Office:Mac

> Hi All, Using Excel 2004 11.2.3
> My problem is replicating the following which works on each sheet. I have
[quoted text clipped - 14 lines]
> to enter the defined name so it works independently in each sheet much
> appreciated. Paul

Reply to this Message

Bob Greenblatt - 23 May 2006 15:15 GMT

On 5/23/06 9:16 AM, in article #l3L2KmfGHA.4080@TK2MSFTNGP03.phx.gbl,

> If I understand your problem, you are finding that range names are *not*
> isolated to individual sheets, but are 'global' Workbook references -
[quoted text clipped - 3 lines]
> Try naming your ranges uniquely such as extend_05, extend_06 and you should
> be OK.

No, I think he has defined the names as global names and really wants
exactly the same name, but specific for each sheet. So, in the define name
dialog, in the name field, you need to type total_2004!extend. Then use the
same definition you already have. Likewise with the Total 2005 sheet
selected, the name should be total_2005!extend, etc.
Then you can use control G (or F5) and type extend and it should highlight
the correct range on each sheet.

Signature

Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom

Reply to this Message

CyberTaz - 23 May 2006 17:08 GMT

No argument - yours is an excellent altenative, and probably more what the
OP was looking for. I've just usually found it easier & less confusing to
work with each range name as a unique entity.

Signature

Regards |:>)
Bob Jones
[MVP] Office:Mac

> On 5/23/06 9:16 AM, in article #l3L2KmfGHA.4080@TK2MSFTNGP03.phx.gbl,
>
[quoted text clipped - 16 lines]
> Then you can use control G (or F5) and type extend and it should highlight
> the correct range on each sheet.

Reply to this Message

Skin - 25 May 2006 10:32 GMT

Thanks for the effort All. Bob has nailed what I wanted. Incidentally I
found another work around :- If you define a name on an original sheet then
copy that sheet via edit - move - copy that defined name will be available
to all sheets copied. Just change the name of the copied sheets.
I am grateful for your input. Paul

On 24/5/06 3:02 AM, in article OubKUJofGHA.5104@TK2MSFTNGP04.phx.gbl,

> No argument - yours is an excellent altenative, and probably more what the
> OP was looking for. I've just usually found it easier & less confusing to
> work with each range name as a unique entity.

Reply to this Message

Mac Forum / Applications / Excel / May 2006

Named Range