Define name

[digicat]

Looking for a way to define a name over several Sheets that will apply
to the active sheet only.
An example:
On Sheet1, Sheet2 and Sheet3 in cells A4:G17 I have a form that I want
to give one name (e.g. OrderForm) that will automatically apply to
that area on the active sheet only.

I tried:
=SHEET1:Sheet3!$A$4:$G$17
but the following problems occur:

- the defined name “OrderForm” does not appear in the Name Box
so how can I use it?
- F3 (Paste Name Dialog Box) and Ctrl+F3 (Define Name Dialog Box)
doesn’t help either

Anybody who can put me on the right track?

 

[Bob Phillips]

Sheet specific names only appear in the names box when that particular sheet
is active.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Dave Peterson]

You could use Jan Karel Pieterse's (with Charles Williams and Matthew Henson)
Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

 

[digicat]

How do I make a sheet specific name?
That is exactly what I'm looking for.
The way I'm trying to do it doesn't work.
The name I have defined with a 3D-formula doesn't show in any of the
sheets and refers only to the first sheet anyway.
=Sheet1:Sheet3!$A$5:$M$52
When I'm building the formula single quote marks show around the
sheetnames, but they disappear when I hit th OK button.
='Sheet1:Sheet3'!$A$5:$M$52

 

[Bob Phillips]

Select the sheet
Goto Name>Insert Name>Define...
In the names type Sheet3!name or whatever the sheet is
Add the range in the RefersTo box
OK

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[digicat]

Hi Bob Philips
Two problems when I try your suggstion:
1. the naam Sheet3!name is not valid - because of the ! in it
2. even with another valid name like Sheet3name, the name still applies
only to the range in Sheet 3 and is visible visible in the namebox in
all sheets

I'm trying to give the same range on multiple sheets one name that will
apply to all sheets.

More suggestions?

 

[Bob Phillips]

Two problems when I try your suggstion:
1. the naam Sheet3!name is not valid - because of the ! in it

It is valid!

2. even with another valid name like Sheet3name, the name still applies
only to the range in Sheet 3 and is visible visible in the namebox in
all sheets

I know that, I told you that earlier.

I'm trying to give the same range on multiple sheets one name that will
apply to all sheets.

You can't do that. You can give the range on each sheet the same name, but
it will only be visible on that sheet, as I explained earlier.