Named Range

[Skin]

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

 

[CyberTaz]

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.

 

[Bob Greenblatt]

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.

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.

 

[CyberTaz]

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.

 

[Skin]

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