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)

"digicat" <[email protected]> wrote in
message news:[email protected]...
>
> 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?
>
>
> --
> digicat
> ------------------------------------------------------------------------
> digicat's Profile:
http://www.excelforum.com/member.php...o&userid=14920
> View this thread: http://www.excelforum.com/showthread...hreadid=502825
>

[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

[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 wrote:
>
> 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?
>
> --
> digicat
> ------------------------------------------------------------------------
> digicat's Profile: http://www.excelforum.com/member.php...o&userid=14920
> View this thread: http://www.excelforum.com/showthread...hreadid=502825

--

Dave Peterson

[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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> digicat
> ------------------------------------------------------------------------
> digicat's Profile:
http://www.excelforum.com/member.php...o&userid=14920
> View this thread: http://www.excelforum.com/showthread...hreadid=502825
>

[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]

"digicat" <[email protected]> wrote in
message news:[email protected]...
>
> 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.