Names issue

D

DL

Hello there.

Platform / office: Windows 98 / Excel 97

I have two worksheets: WS1 and WS2. In the WS1 I have name as UniName.
If I protect WS1 and copy it (by using Ctrl + mouse), the name UniName
will be copied also. If the WS1 is active, in the Define Name box I can
see:
UniName _ _ {some blank space} _ _ WS1

and, if the WS2 is active, Define Name box will looks like:
UniName _ _ {some blank space} _ _ WS2

The field Refers to: will show [=WS1!{some ref}] in the first case and
[=WS2!{some ref}] in the second one.

The question:
How I can create all of this without copying the sheets (suppose I have
already worksheets WS1 and WS2 and want to add to both of them
UniName2)? I'll tried everything but name is not editable: taking the
last value entered in the field Refers to: only.

Thanks in advance.

DL
 
B

Bob Phillips

If I understand you correctly, create a new name by activating each sheet,
going to the names Dialog, adding a name of WS1!UniName, then on WS2,
WS2!UniName. Set refersto as normal.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
R

RagDyeR

If you want to use the same range name on different sheets (WS specific, as
opposed to WB specific), you can simply select the range and then click in
the name box (left of formula bar), and type in:
Sheet1!uniname

Do the same for Sheet2.

If, however, you haven't created the other sheets yet, if you assign Uniname
to Sheet1 in the above manner, and then copy Sheet1 to create Sheet2, Sheet2
will *automatically* have this WS specific name already included.

On the other hand, if you have, say 10 WSs already created, you can create a
WS specific range name for all the sheets at one time (assuming the range is
identical on all sheets) by:

<Insert> <Name> <Define>
And in the "Names In Workbook" box enter:
uniname
And in the "Refers To" box enter:
=Indirect("A1:A5")
Then <OK>.

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

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Hello there.

Platform / office: Windows 98 / Excel 97

I have two worksheets: WS1 and WS2. In the WS1 I have name as UniName.
If I protect WS1 and copy it (by using Ctrl + mouse), the name UniName
will be copied also. If the WS1 is active, in the Define Name box I can
see:
UniName _ _ {some blank space} _ _ WS1

and, if the WS2 is active, Define Name box will looks like:
UniName _ _ {some blank space} _ _ WS2

The field Refers to: will show [=WS1!{some ref}] in the first case and
[=WS2!{some ref}] in the second one.

The question:
How I can create all of this without copying the sheets (suppose I have
already worksheets WS1 and WS2 and want to add to both of them
UniName2)? I'll tried everything but name is not editable: taking the
last value entered in the field Refers to: only.

Thanks in advance.

DL
 
D

DL

Thank you Bob.

Thank you Bob. Strange but seems that work!

WS(whatever)! in the very top names field I'd never try without your
suggestion. Thought that it will be accepted as the name but nope: it
also reffered to WS only and unicel name is present on the propper way.

Thanks again.

DL



Bob Phillips said:
If I understand you correctly, create a new name by activating each sheet,
going to the names Dialog, adding a name of WS1!UniName, then on WS2,
WS2!UniName. Set refersto as normal.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

DL said:
Hello there.

Platform / office: Windows 98 / Excel 97

I have two worksheets: WS1 and WS2. In the WS1 I have name as UniName.
If I protect WS1 and copy it (by using Ctrl + mouse), the name UniName
will be copied also. If the WS1 is active, in the Define Name box I can
see:
UniName _ _ {some blank space} _ _ WS1

and, if the WS2 is active, Define Name box will looks like:
UniName _ _ {some blank space} _ _ WS2

The field Refers to: will show [=WS1!{some ref}] in the first case and
[=WS2!{some ref}] in the second one.

The question:
How I can create all of this without copying the sheets (suppose I have
already worksheets WS1 and WS2 and want to add to both of them
UniName2)? I'll tried everything but name is not editable: taking the
last value entered in the field Refers to: only.

Thanks in advance.

DL
Click to expand...
Click to expand...
 
D

DL

Thanks Rag.

But what if the name refferes to formula? Not on cell. And if it is not
absolute refference? The Bob solution works.

DL
 
B

Bob Phillips

A bit of background reading for you

http://www.xldynamic.com/source/xld.Names.html
Names In Excel - A Discussion


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

DL said:
Thank you Bob.

Thank you Bob. Strange but seems that work!

WS(whatever)! in the very top names field I'd never try without your
suggestion. Thought that it will be accepted as the name but nope: it
also reffered to WS only and unicel name is present on the propper way.

Thanks again.

DL



Bob Phillips said:
If I understand you correctly, create a new name by activating each sheet,
going to the names Dialog, adding a name of WS1!UniName, then on WS2,
WS2!UniName. Set refersto as normal.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

DL said:
Hello there.

Platform / office: Windows 98 / Excel 97

I have two worksheets: WS1 and WS2. In the WS1 I have name as UniName.
If I protect WS1 and copy it (by using Ctrl + mouse), the name UniName
will be copied also. If the WS1 is active, in the Define Name box I can
see:
UniName _ _ {some blank space} _ _ WS1

and, if the WS2 is active, Define Name box will looks like:
UniName _ _ {some blank space} _ _ WS2

The field Refers to: will show [=WS1!{some ref}] in the first case and
[=WS2!{some ref}] in the second one.

The question:
How I can create all of this without copying the sheets (suppose I have
already worksheets WS1 and WS2 and want to add to both of them
UniName2)? I'll tried everything but name is not editable: taking the
last value entered in the field Refers to: only.

Thanks in advance.

DL
Click to expand...
Click to expand...
Click to expand...
 
R

Ragdyer

Makes no difference!
Formulas are treated *exactly* the same.

<Insert> <Name> <Define>
And in the "Names In Workbook" box enter:
uniname*1*
And in the "Refers To" box enter:
=Sum(Indirect("A1:A5"))
Then <OK>
OR
uniname*2*
And in the "Refers To" box enter:
=Average(Indirect("A1:A5"))
Then <OK>
OR
uniname*3*
=VLOOKUP(INDIRECT("D1"),INDIRECT("B1:C10"),2,0)
Then <OK>

*Still* lets you name them (formulas) for all sheets in one shot!

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
DL said:
Thanks Rag.

But what if the name refferes to formula? Not on cell. And if it is not
absolute refference? The Bob solution works.

DL



RagDyeR said:
If you want to use the same range name on different sheets (WS specific, as
opposed to WB specific), you can simply select the range and then click in
the name box (left of formula bar), and type in:
Sheet1!uniname

Do the same for Sheet2.

If, however, you haven't created the other sheets yet, if you assign Uniname
to Sheet1 in the above manner, and then copy Sheet1 to create Sheet2, Sheet2
will *automatically* have this WS specific name already included.

On the other hand, if you have, say 10 WSs already created, you can create a
WS specific range name for all the sheets at one time (assuming the range is
identical on all sheets) by:

<Insert> <Name> <Define>
And in the "Names In Workbook" box enter:
uniname
And in the "Refers To" box enter:
=Indirect("A1:A5")
Then <OK>.

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

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Hello there.

Platform / office: Windows 98 / Excel 97

I have two worksheets: WS1 and WS2. In the WS1 I have name as UniName.
If I protect WS1 and copy it (by using Ctrl + mouse), the name UniName
will be copied also. If the WS1 is active, in the Define Name box I can
see:
UniName _ _ {some blank space} _ _ WS1

and, if the WS2 is active, Define Name box will looks like:
UniName _ _ {some blank space} _ _ WS2

The field Refers to: will show [=WS1!{some ref}] in the first case and
[=WS2!{some ref}] in the second one.

The question:
How I can create all of this without copying the sheets (suppose I have
already worksheets WS1 and WS2 and want to add to both of them
UniName2)? I'll tried everything but name is not editable: taking the
last value entered in the field Refers to: only.

Thanks in advance.

DL
Click to expand...
Click to expand...
 
R

Ragdyer

Should have mentioned that the asterisk numbered suffix I used for "uniname"
was for *emphasis only*, since the asterisk is a reserved character and
*cannot* be used as part of a name.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Ragdyer said:
Makes no difference!
Formulas are treated *exactly* the same.

<Insert> <Name> <Define>
And in the "Names In Workbook" box enter:
uniname*1*
And in the "Refers To" box enter:
=Sum(Indirect("A1:A5"))
Then <OK>
OR
uniname*2*
And in the "Refers To" box enter:
=Average(Indirect("A1:A5"))
Then <OK>
OR
uniname*3*
=VLOOKUP(INDIRECT("D1"),INDIRECT("B1:C10"),2,0)
Then <OK>

*Still* lets you name them (formulas) for all sheets in one shot!

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
DL said:
Thanks Rag.

But what if the name refferes to formula? Not on cell. And if it is not
absolute refference? The Bob solution works.

DL



RagDyeR said:
If you want to use the same range name on different sheets (WS specific, as
opposed to WB specific), you can simply select the range and then click in
the name box (left of formula bar), and type in:
Sheet1!uniname

Do the same for Sheet2.

If, however, you haven't created the other sheets yet, if you assign Uniname
to Sheet1 in the above manner, and then copy Sheet1 to create Sheet2, Sheet2
will *automatically* have this WS specific name already included.

On the other hand, if you have, say 10 WSs already created, you can create a
WS specific range name for all the sheets at one time (assuming the range is
identical on all sheets) by:

<Insert> <Name> <Define>
And in the "Names In Workbook" box enter:
uniname
And in the "Refers To" box enter:
=Indirect("A1:A5")
Then <OK>.

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

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Hello there.

Platform / office: Windows 98 / Excel 97

I have two worksheets: WS1 and WS2. In the WS1 I have name as UniName.
If I protect WS1 and copy it (by using Ctrl + mouse), the name UniName
will be copied also. If the WS1 is active, in the Define Name box I can
see:
UniName _ _ {some blank space} _ _ WS1

and, if the WS2 is active, Define Name box will looks like:
UniName _ _ {some blank space} _ _ WS2

The field Refers to: will show [=WS1!{some ref}] in the first case and
[=WS2!{some ref}] in the second one.

The question:
How I can create all of this without copying the sheets (suppose I have
already worksheets WS1 and WS2 and want to add to both of them
UniName2)? I'll tried everything but name is not editable: taking the
last value entered in the field Refers to: only.

Thanks in advance.

DL
Click to expand...
Click to expand...
Click to expand...
 
D

DL

I am not sure that we are talking about the same things... There are no
uniname1 and uniname2 etc... but uniname only. The only way I've found
so far is to enteret the sheet_name in name field. If you play with
refer_to only, the last value will be accepted.

D.-


Ragdyer said:
Should have mentioned that the asterisk numbered suffix I used for "uniname"
was for *emphasis only*, since the asterisk is a reserved character and
*cannot* be used as part of a name.
--
Regards,

RD

---------------------------------------------------------------------- -----
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------- -----
Ragdyer said:
Makes no difference!
Formulas are treated *exactly* the same.

<Insert> <Name> <Define>
And in the "Names In Workbook" box enter:
uniname*1*
And in the "Refers To" box enter:
=Sum(Indirect("A1:A5"))
Then <OK>
OR
uniname*2*
And in the "Refers To" box enter:
=Average(Indirect("A1:A5"))
Then <OK>
OR
uniname*3*
=VLOOKUP(INDIRECT("D1"),INDIRECT("B1:C10"),2,0)
Then <OK>

*Still* lets you name them (formulas) for all sheets in one shot!

--
HTH,

RD

---------------------------------------------------------------------- -----
Please keep all correspondence within the NewsGroup, so all may
Click to expand...
benefit !

---------------------------------------------------------------------- -----
DL said:
Thanks Rag.

But what if the name refferes to formula? Not on cell. And if it is not
absolute refference? The Bob solution works.

DL



If you want to use the same range name on different sheets (WS
specific, as
opposed to WB specific), you can simply select the range and then
click in
the name box (left of formula bar), and type in:
Sheet1!uniname

Do the same for Sheet2.

If, however, you haven't created the other sheets yet, if you assign
Uniname
to Sheet1 in the above manner, and then copy Sheet1 to create Sheet2,
Sheet2
will *automatically* have this WS specific name already included.

On the other hand, if you have, say 10 WSs already created, you can
create a
WS specific range name for all the sheets at one time (assuming the
range is
identical on all sheets) by:

<Insert> <Name> <Define>
And in the "Names In Workbook" box enter:
uniname
And in the "Refers To" box enter:
=Indirect("A1:A5")
Then <OK>.

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

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Hello there.

Platform / office: Windows 98 / Excel 97

I have two worksheets: WS1 and WS2. In the WS1 I have name as UniName.
If I protect WS1 and copy it (by using Ctrl + mouse), the name UniName
will be copied also. If the WS1 is active, in the Define Name box I
can
see:
UniName _ _ {some blank space} _ _ WS1

and, if the WS2 is active, Define Name box will looks like:
UniName _ _ {some blank space} _ _ WS2

The field Refers to: will show [=WS1!{some ref}] in the first case and
[=WS2!{some ref}] in the second one.

The question:
How I can create all of this without copying the sheets (suppose I
have
already worksheets WS1 and WS2 and want to add to both of them
UniName2)? I'll tried everything but name is not editable: taking the
last value entered in the field Refers to: only.

Thanks in advance.

DL
Click to expand...
Click to expand...
Click to expand...
 
R

Ragdyer

I was only using those names as an example to show you that you could just
as easily, "universally", assign sheet specific names to formulas as well as
cell ranges.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
DL said:
I am not sure that we are talking about the same things... There are no
uniname1 and uniname2 etc... but uniname only. The only way I've found
so far is to enteret the sheet_name in name field. If you play with
refer_to only, the last value will be accepted.

D.-


Ragdyer said:
Should have mentioned that the asterisk numbered suffix I used for "uniname"
was for *emphasis only*, since the asterisk is a reserved character and
*cannot* be used as part of a name.
--
Regards,

RD

---------------------------------------------------------------------- -----
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------- -----
Ragdyer said:
Makes no difference!
Formulas are treated *exactly* the same.

<Insert> <Name> <Define>
And in the "Names In Workbook" box enter:
uniname*1*
And in the "Refers To" box enter:
=Sum(Indirect("A1:A5"))
Then <OK>
OR
uniname*2*
And in the "Refers To" box enter:
=Average(Indirect("A1:A5"))
Then <OK>
OR
uniname*3*
=VLOOKUP(INDIRECT("D1"),INDIRECT("B1:C10"),2,0)
Then <OK>

*Still* lets you name them (formulas) for all sheets in one shot!

--
HTH,

RD

---------------------------------------------------------------------- -----
Please keep all correspondence within the NewsGroup, so all may
Click to expand...
benefit !

---------------------------------------------------------------------- -----
Thanks Rag.

But what if the name refferes to formula? Not on cell. And if it is not
absolute refference? The Bob solution works.

DL



If you want to use the same range name on different sheets (WS
specific, as
opposed to WB specific), you can simply select the range and then
click in
the name box (left of formula bar), and type in:
Sheet1!uniname

Do the same for Sheet2.

If, however, you haven't created the other sheets yet, if you assign
Uniname
to Sheet1 in the above manner, and then copy Sheet1 to create Sheet2,
Sheet2
will *automatically* have this WS specific name already included.

On the other hand, if you have, say 10 WSs already created, you can
create a
WS specific range name for all the sheets at one time (assuming the
range is
identical on all sheets) by:

<Insert> <Name> <Define>
And in the "Names In Workbook" box enter:
uniname
And in the "Refers To" box enter:
=Indirect("A1:A5")
Then <OK>.

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

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Hello there.

Platform / office: Windows 98 / Excel 97

I have two worksheets: WS1 and WS2. In the WS1 I have name as UniName.
If I protect WS1 and copy it (by using Ctrl + mouse), the name UniName
will be copied also. If the WS1 is active, in the Define Name box I
can
see:
UniName _ _ {some blank space} _ _ WS1

and, if the WS2 is active, Define Name box will looks like:
UniName _ _ {some blank space} _ _ WS2

The field Refers to: will show [=WS1!{some ref}] in the first case and
[=WS2!{some ref}] in the second one.

The question:
How I can create all of this without copying the sheets (suppose I
have
already worksheets WS1 and WS2 and want to add to both of them
UniName2)? I'll tried everything but name is not editable: taking the
last value entered in the field Refers to: only.

Thanks in advance.

DL
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA: Moving data from another worksheet 2
Delete sheets code error requires object 2
Subscript out of range 1
macros run twice - why 2
Auto update some spreadsheets inside a shared workbook and NOT others 0
Find last cell in a column, Delete its contents and make it active 5
excel - outlines 4
Linking worksheet and multiple cells 1

Top