range name global

[Guest]

hi community

how can i create a range name that can be use within 2 worksheets,
example

in worksheet 1, i have range from A1:A3 and name that as "Fruits"
also
in worksheet 2, i have range from A1:A3 with similar data and also wanted to
name that as "Fruits"

my objective is as and when I am in worksheet 1 or worksheet 2, all i need
to do is click the Name Box and select "Fruits", excel will select for me the
range (A1:A3) if I am in worksheet 1 and when at worksheet 2, excel will
select A1:A3 in worksheet 2

thanks community for the assistance

 

[Guest]

Select A1:A3 on worksheet1 (let's assume the sheet name is "Worksheet 1"),
click Insert/Name/Define

and incorporate the worksheet name in the name of your range. For example:
name = 'Worksheet 1'!Fruits

repeat the process for sheet2. This way, you can declare several local
named ranges that share the same name. Bob Phillips has some additional info
here:

http://xldynamic.com/source/xld.Names.html

 

[Guest]

thanks "JMB" for the guidance...
perhaps my request was not properly stated...
what I am looking for is..

assumed I had data already entered into range of cells say at

worksheet 1 - range a1:a3 (with data)

worksheet 2 - range a1:a3 (with data - similar with worksheet 1)

how do i declare a name range for both of them without having to manually
selecting worksheet 1 and perform the Name Range steps

thereafter

go into worksheet 2 and repeat the similar steps as for worksheet 1

it is ok for just say 2 or 3 worksheets but if say, I have 25 worksheets and
wanted to have their similar name range (with similar data) all to have a
"Name" and whenever I am in whichever worksheet, all I need to do is go to
the Name Box and click the Name.

hope now this is clearer

or that is no possible way to have it done in this way ???

many thanks for the guidance

thanks to community as well

 

[Gord Dibben]

On sheet1 select A1:A3

Insert>Name>Define>MyName

=!$A$1:$A$3

Note: won't show up in the NameBox but can be entered in F5 dialog box.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

On second thought, this can give you a problem.

The name if used in a formula like =SUM(MyName) is not volatile so won't
update without an F2>ENTER

Best to download Jan Pieterse's Name Manager Add-in.

http://www.oaltd.co.uk/MVP/Default.htm


Gord

 

[oldLearner57]

thanks Gord Dibben for the info and guidance

much appreciated

thanks community as well