A couple of questiosn on Named Ranges

S

Simon Cleal

1) I believe it's possible to use the same name on separate worksheets of the
same workbook but I can't work out how, the Insert ¦ Name command doesn't
seem to give me an option and all names I put in seem to be 'global'

2) I (accidentally) put the name of a cell being referred to in single
quotes and when the cell it was in dragged or copied the name referred to
changed. (ie. the single quotes are doing the opposite of dollar signs on a
normal A1 style cell reference.) However although I did this once
unintentionally I have been unable to repeat what I did

Thanks in advance

Simon
 
R

Ragdyer

To assign a range name that's WS specific, just prefix it with the sheet
name.

For example, select the range and click in the name box (left of formula
bar).
Type:
Sheet1!rng1
<Enter>
 
V

vezerid

Simon,

regarding question #1, you can precede the name with the sheet name as
in
Sheet1!myName
This makes it local to the specific worksheet

Regarding #2, I am eager to see if someone comes up with an answer as
this is new to me as well.

HTH
Kostis Vezerides
 
D

Don Guillett

one way to do this is to do it one sheet and copy that sheet. Defined name
follows to new sheet
 
R

Ragdyer

And if you have a number of sheets *already* in existence, you can assign a
WS specific name "globally" to the *entire* WB, as long as the ranges are
identical, with:

<Insert> <Name> <Define>
And in the "Names In Workbook" box enter:
rng1
And in the "Refers To" box enter the range references with this formula:

=Indirect("A1:A5")
Then <OK>.

This will make the name WS specific on *all* WSs in the WB.

However, if the name in each sheet refers to *different* ranges, then
they'll have to be individually assigned.
 
Top