Named Ranges
 

Note: This is a continuation of a previous discussion which you don't really need.

Dave,

I did an experiment. I opened a brand new workbook. On Sheet1, I selected A1:A10 and defined it with a name "Range123." Then, on Sheet2, I did the same. As soon as I did that, "Range123" from Sheet1 no longer existed. So, I don't really understand how the user can have the same named ranges within one workbook? You know what, I even took out the reference to Sheet and tried to force in just the cell reference; but Excel won't let me. What am I missing here? Wonder if *two* workbooks are involved when we talk about "same name?"

Sounds like you were talking about VBA. I didn't pick this up.

Look forward to your comments.

Epinn

Named Ranges
 

Add a named range to a sheet. Now make a copy of that sheet into the same
workbook (Hold down the Ctrl key and drag and drop the sheet or right click
the sheet and select move or copy and make a copy of the sheet). You will now
have two named ranges with the same name on two different sheets...
--
HTH...

Jim Thomlinson


"Epinn" wrote:

Note: This is a continuation of a previous discussion which you don't really need.

Dave,

I did an experiment. I opened a brand new workbook. On Sheet1, I selected A1:A10 and defined it with a name "Range123." Then, on Sheet2, I did the same. As soon as I did that, "Range123" from Sheet1 no longer existed. So, I don't really understand how the user can have the same named ranges within one workbook? You know what, I even took out the reference to Sheet and tried to force in just the cell reference; but Excel won't let me. What am I missing here? Wonder if *two* workbooks are involved when we talk about "same name?"

Sounds like you were talking about VBA. I didn't pick this up.

Look forward to your comments.

Epinn

Named Ranges
 

If you want to create a worksheet level name on "Sheet 999", you'd use a name
like:

'Sheet 999'!Range123

If you don't include the sheet name, then the name will be global (workbook
level).

Get 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

It'll make it much easier to experiment with names. (In fact, Jan Karel (et al)
included a way to "globalize" or "localize" names.

Epinn wrote:

Note: This is a continuation of a previous discussion which you don't really need.

Dave,

I did an experiment. I opened a brand new workbook. On Sheet1, I selected A1:A10 and defined it with a name "Range123." Then, on Sheet2, I did the same. As soon as I did that, "Range123" from Sheet1 no longer existed. So, I don't really understand how the user can have the same named ranges within one workbook? You know what, I even took out the reference to Sheet and tried to force in just the cell reference; but Excel won't let me. What am I missing here? Wonder if *two* workbooks are involved when we talk about "same name?"

Sounds like you were talking about VBA. I didn't pick this up.

Look forward to your comments.

Epinn

--

Dave Peterson

Named Ranges
 

Epinn,

If you are in Sheet1 and do Insert | Name Define to see you list of
names, you will also see against Range123 the sheet reference. Now
select Sheet2 and repeat, and you will see Range123 with a sheet
reference of Sheet2. They need not be the same ranges on their
respective sheets. If you have two identical names (on different
sheets) then you have to prefix the name with the sheet name to ensure
that you get the correct one (it will default to current sheet if the
sheet name is missing). If you delete one of them then the remaining
one does not need its sheet reference when you refer to it.

Hope this helps.

Pete

Epinn wrote:
Note: This is a continuation of a previous discussion which you don't really need.

Dave,

I did an experiment. I opened a brand new workbook. On Sheet1, I selected A1:A10 and defined it with a name "Range123." Then, on Sheet2, I did the same. As soon as I did that, "Range123" from Sheet1 no longer existed. So, I don't really understand how the user can have the same named ranges within one workbook? You know what, I even took out the reference to Sheet and tried to force in just the cell reference; but Excel won't let me. What am I missing here? Wonder if *two* workbooks are involved when we talk about "same name?"

Sounds like you were talking about VBA. I didn't pick this up.

Look forward to your comments.

Epinn

Named Ranges
 

Didn't see where this started?

I'm not sure this is what you're asking so if it's not just store it in your
memory bank......

You can create one named range that refers to the active sheet only instead
of creating the a named range for each different sheet:

Name: Rng
Refers to: !$A$1:$A$10

So, if you're on sheet1 and you enter this formula: =SUM(Rng), it'll refer
to sheet1. If you're on sheet10 and enter the same formula it'll refer to
sheet10.

Biff

"Epinn" wrote in message
...
Note: This is a continuation of a previous discussion which you don't
really need.

Dave,

I did an experiment. I opened a brand new workbook. On Sheet1, I selected
A1:A10 and defined it with a name "Range123." Then, on Sheet2, I did the
same. As soon as I did that, "Range123" from Sheet1 no longer existed. So,
I don't really understand how the user can have the same named ranges within
one workbook? You know what, I even took out the reference to Sheet and
tried to force in just the cell reference; but Excel won't let me. What am
I missing here? Wonder if *two* workbooks are involved when we talk about
"same name?"

Sounds like you were talking about VBA. I didn't pick this up.

Look forward to your comments.

Epinn

Named Ranges
 

I've seen posts where this type of name will cause trouble (calculation problems
when VBA calculates, IIRC).

Maybe it would be better to use:

=indirect("a1:a10")



Biff wrote:

Didn't see where this started?

I'm not sure this is what you're asking so if it's not just store it in your
memory bank......

You can create one named range that refers to the active sheet only instead
of creating the a named range for each different sheet:

Name: Rng
Refers to: !$A$1:$A$10

So, if you're on sheet1 and you enter this formula: =SUM(Rng), it'll refer
to sheet1. If you're on sheet10 and enter the same formula it'll refer to
sheet10.

Biff

"Epinn" wrote in message
...
Note: This is a continuation of a previous discussion which you don't
really need.

Dave,

I did an experiment. I opened a brand new workbook. On Sheet1, I selected
A1:A10 and defined it with a name "Range123." Then, on Sheet2, I did the
same. As soon as I did that, "Range123" from Sheet1 no longer existed. So,
I don't really understand how the user can have the same named ranges within
one workbook? You know what, I even took out the reference to Sheet and
tried to force in just the cell reference; but Excel won't let me. What am
I missing here? Wonder if *two* workbooks are involved when we talk about
"same name?"

Sounds like you were talking about VBA. I didn't pick this up.

Look forward to your comments.

Epinn

--

Dave Peterson

Named Ranges
 

Do you mean when that type of name is used in the code itself? If so, then I
wouldn't "blame" the name I'd "blame" the coder.

Biff

"Dave Peterson" wrote in message
...
I've seen posts where this type of name will cause trouble (calculation
problems
when VBA calculates, IIRC).

Maybe it would be better to use:

=indirect("a1:a10")



Biff wrote:

Didn't see where this started?

I'm not sure this is what you're asking so if it's not just store it in
your
memory bank......

You can create one named range that refers to the active sheet only
instead
of creating the a named range for each different sheet:

Name: Rng
Refers to: !$A$1:$A$10

So, if you're on sheet1 and you enter this formula: =SUM(Rng), it'll
refer
to sheet1. If you're on sheet10 and enter the same formula it'll refer to
sheet10.

Biff

"Epinn" wrote in message
...
Note: This is a continuation of a previous discussion which you don't
really need.

Dave,

I did an experiment. I opened a brand new workbook. On Sheet1, I
selected
A1:A10 and defined it with a name "Range123." Then, on Sheet2, I did the
same. As soon as I did that, "Range123" from Sheet1 no longer existed.
So,
I don't really understand how the user can have the same named ranges
within
one workbook? You know what, I even took out the reference to Sheet and
tried to force in just the cell reference; but Excel won't let me. What
am
I missing here? Wonder if *two* workbooks are involved when we talk
about
"same name?"

Sounds like you were talking about VBA. I didn't pick this up.

Look forward to your comments.

Epinn

--

Dave Peterson

Named Ranges
 

Nope. I mean that names that look like !$a$1:$a$10 can cause trouble with
calculation. Nothing to do with if the name is actually used in the code
itself.

http://groups.google.co.uk/group/mic...e808 b242f714

or
http://snipurl.com/yp9w

I searched google, but couldn't find the thread that I wanted.

Biff wrote:

Do you mean when that type of name is used in the code itself? If so, then I
wouldn't "blame" the name I'd "blame" the coder.

Biff

"Dave Peterson" wrote in message
...
I've seen posts where this type of name will cause trouble (calculation
problems
when VBA calculates, IIRC).

Maybe it would be better to use:

=indirect("a1:a10")



Biff wrote:

Didn't see where this started?

I'm not sure this is what you're asking so if it's not just store it in
your
memory bank......

You can create one named range that refers to the active sheet only
instead
of creating the a named range for each different sheet:

Name: Rng
Refers to: !$A$1:$A$10

So, if you're on sheet1 and you enter this formula: =SUM(Rng), it'll
refer
to sheet1. If you're on sheet10 and enter the same formula it'll refer to
sheet10.

Biff

"Epinn" wrote in message
...
Note: This is a continuation of a previous discussion which you don't
really need.

Dave,

I did an experiment. I opened a brand new workbook. On Sheet1, I
selected
A1:A10 and defined it with a name "Range123." Then, on Sheet2, I did the
same. As soon as I did that, "Range123" from Sheet1 no longer existed.
So,
I don't really understand how the user can have the same named ranges
within
one workbook? You know what, I even took out the reference to Sheet and
tried to force in just the cell reference; but Excel won't let me. What
am
I missing here? Wonder if *two* workbooks are involved when we talk
about
"same name?"

Sounds like you were talking about VBA. I didn't pick this up.

Look forward to your comments.

Epinn

--

Dave Peterson

--

Dave Peterson

Named Ranges
 

I see. Well, I don't know enough VBA to play around with it and see what's
doin'.

Biff

"Dave Peterson" wrote in message
...
Nope. I mean that names that look like !$a$1:$a$10 can cause trouble with
calculation. Nothing to do with if the name is actually used in the code
itself.

http://groups.google.co.uk/group/mic...e808 b242f714

or
http://snipurl.com/yp9w

I searched google, but couldn't find the thread that I wanted.

Biff wrote:

Do you mean when that type of name is used in the code itself? If so,
then I
wouldn't "blame" the name I'd "blame" the coder.

Biff

"Dave Peterson" wrote in message
...
I've seen posts where this type of name will cause trouble (calculation
problems
when VBA calculates, IIRC).

Maybe it would be better to use:

=indirect("a1:a10")



Biff wrote:

Didn't see where this started?

I'm not sure this is what you're asking so if it's not just store it
in
your
memory bank......

You can create one named range that refers to the active sheet only
instead
of creating the a named range for each different sheet:

Name: Rng
Refers to: !$A$1:$A$10

So, if you're on sheet1 and you enter this formula: =SUM(Rng), it'll
refer
to sheet1. If you're on sheet10 and enter the same formula it'll refer
to
sheet10.

Biff

"Epinn" wrote in message
...
Note: This is a continuation of a previous discussion which you don't
really need.

Dave,

I did an experiment. I opened a brand new workbook. On Sheet1, I
selected
A1:A10 and defined it with a name "Range123." Then, on Sheet2, I did
the
same. As soon as I did that, "Range123" from Sheet1 no longer
existed.
So,
I don't really understand how the user can have the same named ranges
within
one workbook? You know what, I even took out the reference to Sheet
and
tried to force in just the cell reference; but Excel won't let me.
What
am
I missing here? Wonder if *two* workbooks are involved when we talk
about
"same name?"

Sounds like you were talking about VBA. I didn't pick this up.

Look forward to your comments.

Epinn

--

Dave Peterson

--

Dave Peterson

Named Ranges
 

And if I'd remember the details, you'd have a reason.

But it's one of those things that's in my brain as "don't do". The reason is
lost in google somewhere.

Biff wrote:

I see. Well, I don't know enough VBA to play around with it and see what's
doin'.

Biff

"Dave Peterson" wrote in message
...
Nope. I mean that names that look like !$a$1:$a$10 can cause trouble with
calculation. Nothing to do with if the name is actually used in the code
itself.

http://groups.google.co.uk/group/mic...e808 b242f714

or
http://snipurl.com/yp9w

I searched google, but couldn't find the thread that I wanted.

Biff wrote:

Do you mean when that type of name is used in the code itself? If so,
then I
wouldn't "blame" the name I'd "blame" the coder.

Biff

"Dave Peterson" wrote in message
...
I've seen posts where this type of name will cause trouble (calculation
problems
when VBA calculates, IIRC).

Maybe it would be better to use:

=indirect("a1:a10")



Biff wrote:

Didn't see where this started?

I'm not sure this is what you're asking so if it's not just store it
in
your
memory bank......

You can create one named range that refers to the active sheet only
instead
of creating the a named range for each different sheet:

Name: Rng
Refers to: !$A$1:$A$10

So, if you're on sheet1 and you enter this formula: =SUM(Rng), it'll
refer
to sheet1. If you're on sheet10 and enter the same formula it'll refer
to
sheet10.

Biff

"Epinn" wrote in message
...
Note: This is a continuation of a previous discussion which you don't
really need.

Dave,

I did an experiment. I opened a brand new workbook. On Sheet1, I
selected
A1:A10 and defined it with a name "Range123." Then, on Sheet2, I did
the
same. As soon as I did that, "Range123" from Sheet1 no longer
existed.
So,
I don't really understand how the user can have the same named ranges
within
one workbook? You know what, I even took out the reference to Sheet
and
tried to force in just the cell reference; but Excel won't let me.
What
am
I missing here? Wonder if *two* workbooks are involved when we talk
about
"same name?"

Sounds like you were talking about VBA. I didn't pick this up.

Look forward to your comments.

Epinn

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Named Ranges
 

Thank you all for your response. What I want is very simple, nothing to do with VBA.

Basically, I was trying to have the *same name* for A1:A10 on two sheets (within the same workbook) respectively. Jim and Biff gave me the answer.

I don't think I can name A1:A10 on Sheet1 as "ABC" and then also name B1:B10 on Sheet2 as "ABC" as well. I used insertnamedefine and I also use the Sheet# to qualify the range, but no luck.

Can someone confirm this is not possible please?

The Name Manager may come in handy one of these days. Thanks Dave.

Epinn

"Epinn" wrote in message ...
Note: This is a continuation of a previous discussion which you don't really need.

Dave,

I did an experiment. I opened a brand new workbook. On Sheet1, I selected A1:A10 and defined it with a name "Range123." Then, on Sheet2, I did the same. As soon as I did that, "Range123" from Sheet1 no longer existed. So, I don't really understand how the user can have the same named ranges within one workbook? You know what, I even took out the reference to Sheet and tried to force in just the cell reference; but Excel won't let me. What am I missing here? Wonder if *two* workbooks are involved when we talk about "same name?"

Sounds like you were talking about VBA. I didn't pick this up.

Look forward to your comments.

Epinn

Named Ranges
 

Hi Epinn
Can someone confirm this is not possible please?

That is correct.
You can't have the same name for 2 different ranges, either on the same
sheet or different sheets.

You can have the same range on multiple sheets adopt the same name
provided there is only an exclamation "!" in front of the range, not
Sheet!

--
Regards

Roger Govier


"Epinn" wrote in message
...
Thank you all for your response. What I want is very simple, nothing to
do with VBA.

Basically, I was trying to have the *same name* for A1:A10 on two sheets
(within the same workbook) respectively. Jim and Biff gave me the
answer.

I don't think I can name A1:A10 on Sheet1 as "ABC" and then also name
B1:B10 on Sheet2 as "ABC" as well. I used insertnamedefine and I also
use the Sheet# to qualify the range, but no luck.

Can someone confirm this is not possible please?

The Name Manager may come in handy one of these days. Thanks Dave.

Epinn

"Epinn" wrote in message
...
Note: This is a continuation of a previous discussion which you don't
really need.

Dave,

I did an experiment. I opened a brand new workbook. On Sheet1, I
selected A1:A10 and defined it with a name "Range123." Then, on Sheet2,
I did the same. As soon as I did that, "Range123" from Sheet1 no longer
existed. So, I don't really understand how the user can have the same
named ranges within one workbook? You know what, I even took out the
reference to Sheet and tried to force in just the cell reference; but
Excel won't let me. What am I missing here? Wonder if *two* workbooks
are involved when we talk about "same name?"

Sounds like you were talking about VBA. I didn't pick this up.

Look forward to your comments.

Epinn

Named Ranges
 

If you include the sheet name in the definition, you can have sheet level names
that all use that same ABC name.

While on Sheet1:
Insert Name|Define
Names in workbook: sheet1!ABC
refers to: $a$1

While on Sheet 99 (notice the space):
Insert Name|Define
Names in workbook: 'sheet 99'!ABC (notice the apostrophes)
refers to: $C$12 (it can refer to the same address or not)

I can refer to those range names on sheet7653 by:
=sheet1!ABC+'sheet 99'!ABC

And it'll work fine.



Roger Govier wrote:

Hi Epinn
Can someone confirm this is not possible please?

That is correct.
You can't have the same name for 2 different ranges, either on the same
sheet or different sheets.

You can have the same range on multiple sheets adopt the same name
provided there is only an exclamation "!" in front of the range, not
Sheet!

--
Regards

Roger Govier

"Epinn" wrote in message
...
Thank you all for your response. What I want is very simple, nothing to
do with VBA.

Basically, I was trying to have the *same name* for A1:A10 on two sheets
(within the same workbook) respectively. Jim and Biff gave me the
answer.

I don't think I can name A1:A10 on Sheet1 as "ABC" and then also name
B1:B10 on Sheet2 as "ABC" as well. I used insertnamedefine and I also
use the Sheet# to qualify the range, but no luck.

Can someone confirm this is not possible please?

The Name Manager may come in handy one of these days. Thanks Dave.

Epinn

"Epinn" wrote in message
...
Note: This is a continuation of a previous discussion which you don't
really need.

Dave,

I did an experiment. I opened a brand new workbook. On Sheet1, I
selected A1:A10 and defined it with a name "Range123." Then, on Sheet2,
I did the same. As soon as I did that, "Range123" from Sheet1 no longer
existed. So, I don't really understand how the user can have the same
named ranges within one workbook? You know what, I even took out the
reference to Sheet and tried to force in just the cell reference; but
Excel won't let me. What am I missing here? Wonder if *two* workbooks
are involved when we talk about "same name?"

Sounds like you were talking about VBA. I didn't pick this up.

Look forward to your comments.

Epinn

--

Dave Peterson

Named Ranges
 

You can see the same effect if you set up a print area on different
sheets - the same name "Print_Area" is defined in each sheet, but need
not be the same range from one sheet to another.

Pete

Dave Peterson wrote:
If you include the sheet name in the definition, you can have sheet level names
that all use that same ABC name.

While on Sheet1:
Insert Name|Define
Names in workbook: sheet1!ABC
refers to: $a$1

While on Sheet 99 (notice the space):
Insert Name|Define
Names in workbook: 'sheet 99'!ABC (notice the apostrophes)
refers to: $C$12 (it can refer to the same address or not)

I can refer to those range names on sheet7653 by:
=sheet1!ABC+'sheet 99'!ABC

And it'll work fine.



Roger Govier wrote:

Hi Epinn
Can someone confirm this is not possible please?

That is correct.
You can't have the same name for 2 different ranges, either on the same
sheet or different sheets.

You can have the same range on multiple sheets adopt the same name
provided there is only an exclamation "!" in front of the range, not
Sheet!

--
Regards

Roger Govier

"Epinn" wrote in message
...
Thank you all for your response. What I want is very simple, nothing to
do with VBA.

Basically, I was trying to have the *same name* for A1:A10 on two sheets
(within the same workbook) respectively. Jim and Biff gave me the
answer.

I don't think I can name A1:A10 on Sheet1 as "ABC" and then also name
B1:B10 on Sheet2 as "ABC" as well. I used insertnamedefine and I also
use the Sheet# to qualify the range, but no luck.

Can someone confirm this is not possible please?

The Name Manager may come in handy one of these days. Thanks Dave.

Epinn

"Epinn" wrote in message
...
Note: This is a continuation of a previous discussion which you don't
really need.

Dave,

I did an experiment. I opened a brand new workbook. On Sheet1, I
selected A1:A10 and defined it with a name "Range123." Then, on Sheet2,
I did the same. As soon as I did that, "Range123" from Sheet1 no longer
existed. So, I don't really understand how the user can have the same
named ranges within one workbook? You know what, I even took out the
reference to Sheet and tried to force in just the cell reference; but
Excel won't let me. What am I missing here? Wonder if *two* workbooks
are involved when we talk about "same name?"

Sounds like you were talking about VBA. I didn't pick this up.

Look forward to your comments.

Epinn

--

Dave Peterson

Named Ranges
 

Hi Dave

Quite right, but in my eyes those are different named ranges.
Epinn was asking about the same name - which is what I was answering -
and in that case you cannot use the same name to point to 2 different
ranges.

--
Regards

Roger Govier


"Dave Peterson" wrote in message
...
If you include the sheet name in the definition, you can have sheet
level names
that all use that same ABC name.

While on Sheet1:
Insert Name|Define
Names in workbook: sheet1!ABC
refers to: $a$1

While on Sheet 99 (notice the space):
Insert Name|Define
Names in workbook: 'sheet 99'!ABC (notice the apostrophes)
refers to: $C$12 (it can refer to the same address
or not)

I can refer to those range names on sheet7653 by:
=sheet1!ABC+'sheet 99'!ABC

And it'll work fine.



Roger Govier wrote:

Hi Epinn
Can someone confirm this is not possible please?

That is correct.
You can't have the same name for 2 different ranges, either on the
same
sheet or different sheets.

You can have the same range on multiple sheets adopt the same name
provided there is only an exclamation "!" in front of the range, not
Sheet!

--
Regards

Roger Govier

"Epinn" wrote in message
...
Thank you all for your response. What I want is very simple, nothing
to
do with VBA.

Basically, I was trying to have the *same name* for A1:A10 on two
sheets
(within the same workbook) respectively. Jim and Biff gave me the
answer.

I don't think I can name A1:A10 on Sheet1 as "ABC" and then also name
B1:B10 on Sheet2 as "ABC" as well. I used insertnamedefine and I
also
use the Sheet# to qualify the range, but no luck.

Can someone confirm this is not possible please?

The Name Manager may come in handy one of these days. Thanks Dave.

Epinn

"Epinn" wrote in message
...
Note: This is a continuation of a previous discussion which you
don't
really need.

Dave,

I did an experiment. I opened a brand new workbook. On Sheet1, I
selected A1:A10 and defined it with a name "Range123." Then, on
Sheet2,
I did the same. As soon as I did that, "Range123" from Sheet1 no
longer
existed. So, I don't really understand how the user can have the
same
named ranges within one workbook? You know what, I even took out the
reference to Sheet and tried to force in just the cell reference; but
Excel won't let me. What am I missing here? Wonder if *two*
workbooks
are involved when we talk about "same name?"

Sounds like you were talking about VBA. I didn't pick this up.

Look forward to your comments.

Epinn

--

Dave Peterson

Named Ranges
 

I guess it boils down to what you consider the name.

If you create a worksheet level name on Sheet1 named ABC and another worksheet
level name on Sheet2 also named ABC, do they share the same name?



Roger Govier wrote:

Hi Dave

Quite right, but in my eyes those are different named ranges.
Epinn was asking about the same name - which is what I was answering -
and in that case you cannot use the same name to point to 2 different
ranges.

--
Regards

Roger Govier

"Dave Peterson" wrote in message
...
If you include the sheet name in the definition, you can have sheet
level names
that all use that same ABC name.

While on Sheet1:
Insert Name|Define
Names in workbook: sheet1!ABC
refers to: $a$1

While on Sheet 99 (notice the space):
Insert Name|Define
Names in workbook: 'sheet 99'!ABC (notice the apostrophes)
refers to: $C$12 (it can refer to the same address
or not)

I can refer to those range names on sheet7653 by:
=sheet1!ABC+'sheet 99'!ABC

And it'll work fine.



Roger Govier wrote:

Hi Epinn
Can someone confirm this is not possible please?

That is correct.
You can't have the same name for 2 different ranges, either on the
same
sheet or different sheets.

You can have the same range on multiple sheets adopt the same name
provided there is only an exclamation "!" in front of the range, not
Sheet!

--
Regards

Roger Govier

"Epinn" wrote in message
...
Thank you all for your response. What I want is very simple, nothing
to
do with VBA.

Basically, I was trying to have the *same name* for A1:A10 on two
sheets
(within the same workbook) respectively. Jim and Biff gave me the
answer.

I don't think I can name A1:A10 on Sheet1 as "ABC" and then also name
B1:B10 on Sheet2 as "ABC" as well. I used insertnamedefine and I
also
use the Sheet# to qualify the range, but no luck.

Can someone confirm this is not possible please?

The Name Manager may come in handy one of these days. Thanks Dave.

Epinn

"Epinn" wrote in message
...
Note: This is a continuation of a previous discussion which you
don't
really need.

Dave,

I did an experiment. I opened a brand new workbook. On Sheet1, I
selected A1:A10 and defined it with a name "Range123." Then, on
Sheet2,
I did the same. As soon as I did that, "Range123" from Sheet1 no
longer
existed. So, I don't really understand how the user can have the
same
named ranges within one workbook? You know what, I even took out the
reference to Sheet and tried to force in just the cell reference; but
Excel won't let me. What am I missing here? Wonder if *two*
workbooks
are involved when we talk about "same name?"

Sounds like you were talking about VBA. I didn't pick this up.

Look forward to your comments.

Epinn

--

Dave Peterson

--

Dave Peterson

Named Ranges
 

do they share the same name?

Not if they rely on Sheet(n)! in front of them to operate, IMO.

--
Regards

Roger Govier


"Dave Peterson" wrote in message
...
I guess it boils down to what you consider the name.

If you create a worksheet level name on Sheet1 named ABC and another
worksheet
level name on Sheet2 also named ABC, do they share the same name?



Roger Govier wrote:

Hi Dave

Quite right, but in my eyes those are different named ranges.
Epinn was asking about the same name - which is what I was
answering -
and in that case you cannot use the same name to point to 2 different
ranges.

--
Regards

Roger Govier

"Dave Peterson" wrote in message
...
If you include the sheet name in the definition, you can have sheet
level names
that all use that same ABC name.

While on Sheet1:
Insert Name|Define
Names in workbook: sheet1!ABC
refers to: $a$1

While on Sheet 99 (notice the space):
Insert Name|Define
Names in workbook: 'sheet 99'!ABC (notice the apostrophes)
refers to: $C$12 (it can refer to the same
address
or not)

I can refer to those range names on sheet7653 by:
=sheet1!ABC+'sheet 99'!ABC

And it'll work fine.



Roger Govier wrote:

Hi Epinn
Can someone confirm this is not possible please?

That is correct.
You can't have the same name for 2 different ranges, either on the
same
sheet or different sheets.

You can have the same range on multiple sheets adopt the same name
provided there is only an exclamation "!" in front of the range,
not
Sheet!

--
Regards

Roger Govier

"Epinn" wrote in message
...
Thank you all for your response. What I want is very simple,
nothing
to
do with VBA.

Basically, I was trying to have the *same name* for A1:A10 on two
sheets
(within the same workbook) respectively. Jim and Biff gave me the
answer.

I don't think I can name A1:A10 on Sheet1 as "ABC" and then also
name
B1:B10 on Sheet2 as "ABC" as well. I used insertnamedefine and
I
also
use the Sheet# to qualify the range, but no luck.

Can someone confirm this is not possible please?

The Name Manager may come in handy one of these days. Thanks
Dave.

Epinn

"Epinn" wrote in message
...
Note: This is a continuation of a previous discussion which you
don't
really need.

Dave,

I did an experiment. I opened a brand new workbook. On Sheet1, I
selected A1:A10 and defined it with a name "Range123." Then, on
Sheet2,
I did the same. As soon as I did that, "Range123" from Sheet1 no
longer
existed. So, I don't really understand how the user can have the
same
named ranges within one workbook? You know what, I even took out
the
reference to Sheet and tried to force in just the cell reference;
but
Excel won't let me. What am I missing here? Wonder if *two*
workbooks
are involved when we talk about "same name?"

Sounds like you were talking about VBA. I didn't pick this up.

Look forward to your comments.

Epinn

--

Dave Peterson

--

Dave Peterson

Named Ranges
 

I think you're in the minority here.

But it does prove that it's good to specify what you mean if there's any doubt.

Roger Govier wrote:

do they share the same name?

Not if they rely on Sheet(n)! in front of them to operate, IMO.

--
Regards

Roger Govier

"Dave Peterson" wrote in message
...
I guess it boils down to what you consider the name.

If you create a worksheet level name on Sheet1 named ABC and another
worksheet
level name on Sheet2 also named ABC, do they share the same name?



Roger Govier wrote:

Hi Dave

Quite right, but in my eyes those are different named ranges.
Epinn was asking about the same name - which is what I was
answering -
and in that case you cannot use the same name to point to 2 different
ranges.

--
Regards

Roger Govier

"Dave Peterson" wrote in message
...
If you include the sheet name in the definition, you can have sheet
level names
that all use that same ABC name.

While on Sheet1:
Insert Name|Define
Names in workbook: sheet1!ABC
refers to: $a$1

While on Sheet 99 (notice the space):
Insert Name|Define
Names in workbook: 'sheet 99'!ABC (notice the apostrophes)
refers to: $C$12 (it can refer to the same
address
or not)

I can refer to those range names on sheet7653 by:
=sheet1!ABC+'sheet 99'!ABC

And it'll work fine.



Roger Govier wrote:

Hi Epinn
Can someone confirm this is not possible please?

That is correct.
You can't have the same name for 2 different ranges, either on the
same
sheet or different sheets.

You can have the same range on multiple sheets adopt the same name
provided there is only an exclamation "!" in front of the range,
not
Sheet!

--
Regards

Roger Govier

"Epinn" wrote in message
...
Thank you all for your response. What I want is very simple,
nothing
to
do with VBA.

Basically, I was trying to have the *same name* for A1:A10 on two
sheets
(within the same workbook) respectively. Jim and Biff gave me the
answer.

I don't think I can name A1:A10 on Sheet1 as "ABC" and then also
name
B1:B10 on Sheet2 as "ABC" as well. I used insertnamedefine and
I
also
use the Sheet# to qualify the range, but no luck.

Can someone confirm this is not possible please?

The Name Manager may come in handy one of these days. Thanks
Dave.

Epinn

"Epinn" wrote in message
...
Note: This is a continuation of a previous discussion which you
don't
really need.

Dave,

I did an experiment. I opened a brand new workbook. On Sheet1, I
selected A1:A10 and defined it with a name "Range123." Then, on
Sheet2,
I did the same. As soon as I did that, "Range123" from Sheet1 no
longer
existed. So, I don't really understand how the user can have the
same
named ranges within one workbook? You know what, I even took out
the
reference to Sheet and tried to force in just the cell reference;
but
Excel won't let me. What am I missing here? Wonder if *two*
workbooks
are involved when we talk about "same name?"

Sounds like you were talking about VBA. I didn't pick this up.

Look forward to your comments.

Epinn

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Named Ranges
 

Finally, I think the light bulb is ___! Glad Roger understood my question and that the dialogue between Dave and Roger helped me sort out a few things. Yes, how do we define "same name?" I'll leave it for another day. The important thing here is I am quite clear now on how to define names.



Yesterday, I said, "Jim and Biff gave me the answer." Today, I said, "Dave and Pete gave me the answer yesterday as well." Sorry for the delay. It took me a while to grasp what you were trying to tell me. I did read about qualifying the range name by the sheet. Unfortunately, I had a different frame of reference. I focused on the bottom part of the "define name" dialogue box which is "Refers to" while you referred to the top part of the dialogue box "Names in workbook." I didn't know that I had to actually key in "Sheet2!" as part of the name. This is why I told you I had no luck using the same name via insertnamedefine etc.



Now I have a question on apostrophes.



If I do ctrl+drag of a sheet tab that contains a named range and if I *rename* the duplicate (new) sheet, (e.g. newcopy) then there won't be apostrophes around the sheet name (newcopy) in the define name dialogue box. But if I don't rename the duplicate sheet, say I keep sheet 1(2) as the name, then there will be apostrophes around the sheet name in the define name dialogue box; e.g. 'sheet 1(2)'



I find this confusing.



Dave, you included the following in your posts.



=sheet1!ABC+'sheet 99'!ABC



'sheet 99'!ABC (notice the apostrophes)



Yes, I pay attention to the apostrophes but I don't understand why sheet99 has apostrophes and sheet1 doesn't. When I include the sheet name without apostrophes, everything seems to work fine as well. Do I really need apostrophes? I am missing something here.



Please help.



Epinn



"Dave Peterson" wrote in message ...
I think you're in the minority here.

But it does prove that it's good to specify what you mean if there's any doubt.

Roger Govier wrote:

do they share the same name?

Not if they rely on Sheet(n)! in front of them to operate, IMO.

--
Regards

Roger Govier

"Dave Peterson" wrote in message
...
I guess it boils down to what you consider the name.

If you create a worksheet level name on Sheet1 named ABC and another
worksheet
level name on Sheet2 also named ABC, do they share the same name?



Roger Govier wrote:

Hi Dave

Quite right, but in my eyes those are different named ranges.
Epinn was asking about the same name - which is what I was
answering -
and in that case you cannot use the same name to point to 2 different
ranges.

--
Regards

Roger Govier

"Dave Peterson" wrote in message
...
If you include the sheet name in the definition, you can have sheet
level names
that all use that same ABC name.

While on Sheet1:
Insert Name|Define
Names in workbook: sheet1!ABC
refers to: $a$1

While on Sheet 99 (notice the space):
Insert Name|Define
Names in workbook: 'sheet 99'!ABC (notice the apostrophes)
refers to: $C$12 (it can refer to the same
address
or not)

I can refer to those range names on sheet7653 by:
=sheet1!ABC+'sheet 99'!ABC

And it'll work fine.



Roger Govier wrote:

Hi Epinn
Can someone confirm this is not possible please?

That is correct.
You can't have the same name for 2 different ranges, either on the
same
sheet or different sheets.

You can have the same range on multiple sheets adopt the same name
provided there is only an exclamation "!" in front of the range,
not
Sheet!

--
Regards

Roger Govier

"Epinn" wrote in message
...
Thank you all for your response. What I want is very simple,
nothing
to
do with VBA.

Basically, I was trying to have the *same name* for A1:A10 on two
sheets
(within the same workbook) respectively. Jim and Biff gave me the
answer.

I don't think I can name A1:A10 on Sheet1 as "ABC" and then also
name
B1:B10 on Sheet2 as "ABC" as well. I used insertnamedefine and
I
also
use the Sheet# to qualify the range, but no luck.

Can someone confirm this is not possible please?

The Name Manager may come in handy one of these days. Thanks
Dave.

Epinn

"Epinn" wrote in message
...
Note: This is a continuation of a previous discussion which you
don't
really need.

Dave,

I did an experiment. I opened a brand new workbook. On Sheet1, I
selected A1:A10 and defined it with a name "Range123." Then, on
Sheet2,
I did the same. As soon as I did that, "Range123" from Sheet1 no
longer
existed. So, I don't really understand how the user can have the
same
named ranges within one workbook? You know what, I even took out
the
reference to Sheet and tried to force in just the cell reference;
but
Excel won't let me. What am I missing here? Wonder if *two*
workbooks
are involved when we talk about "same name?"

Sounds like you were talking about VBA. I didn't pick this up.

Look forward to your comments.

Epinn

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Named Ranges
 

Hi Epinn

If there are any spaces in a sheet name, apostrophe's must be used.
If there no spaces, apostrophe's are not essential but cause no problem
if they are included when typing a formula.

Even if you include them when you create a named range on sheet1 which
referred to say 'Sheet1'!$A$1:$G$20, then Excel removes them and stores
it as Sheet1$A$1:$G$20.
If you then rename sheet1 to My Sheet, Excel will automatically insert
them when it carries out the internal renaming and you will see 'My
Sheet'!$A$1:$G$20 as the new range when you look at named ranges.

When you type a range in a formula, as opposed to referring to its name,
then you MUST use apostrophe's when referring to the sheet name it there
are spaces, otherwise the formula will fail.

--
Regards

Roger Govier


"Epinn" wrote in message
...
Finally, I think the light bulb is ___! Glad Roger understood my
question and that the dialogue between Dave and Roger helped me sort out
a few things. Yes, how do we define "same name?" I'll leave it for
another day. The important thing here is I am quite clear now on how to
define names.



Yesterday, I said, "Jim and Biff gave me the answer." Today, I said,
"Dave and Pete gave me the answer yesterday as well." Sorry for the
delay. It took me a while to grasp what you were trying to tell me. I
did read about qualifying the range name by the sheet. Unfortunately, I
had a different frame of reference. I focused on the bottom part of the
"define name" dialogue box which is "Refers to" while you referred to
the top part of the dialogue box "Names in workbook." I didn't know
that I had to actually key in "Sheet2!" as part of the name. This is
why I told you I had no luck using the same name via insertnamedefine
etc.



Now I have a question on apostrophes.



If I do ctrl+drag of a sheet tab that contains a named range and if I
*rename* the duplicate (new) sheet, (e.g. newcopy) then there won't be
apostrophes around the sheet name (newcopy) in the define name dialogue
box. But if I don't rename the duplicate sheet, say I keep sheet 1(2)
as the name, then there will be apostrophes around the sheet name in the
define name dialogue box; e.g. 'sheet 1(2)'



I find this confusing.



Dave, you included the following in your posts.



=sheet1!ABC+'sheet 99'!ABC



'sheet 99'!ABC (notice the apostrophes)



Yes, I pay attention to the apostrophes but I don't understand why
sheet99 has apostrophes and sheet1 doesn't. When I include the sheet
name without apostrophes, everything seems to work fine as well. Do I
really need apostrophes? I am missing something here.



Please help.



Epinn



"Dave Peterson" wrote in message
...
I think you're in the minority here.

But it does prove that it's good to specify what you mean if there's any
doubt.

Roger Govier wrote:

do they share the same name?

Not if they rely on Sheet(n)! in front of them to operate, IMO.

--
Regards

Roger Govier

"Dave Peterson" wrote in message
...
I guess it boils down to what you consider the name.

If you create a worksheet level name on Sheet1 named ABC and another
worksheet
level name on Sheet2 also named ABC, do they share the same name?



Roger Govier wrote:

Hi Dave

Quite right, but in my eyes those are different named ranges.
Epinn was asking about the same name - which is what I was
answering -
and in that case you cannot use the same name to point to 2
different
ranges.

--
Regards

Roger Govier

"Dave Peterson" wrote in message
...
If you include the sheet name in the definition, you can have
sheet
level names
that all use that same ABC name.

While on Sheet1:
Insert Name|Define
Names in workbook: sheet1!ABC
refers to: $a$1

While on Sheet 99 (notice the space):
Insert Name|Define
Names in workbook: 'sheet 99'!ABC (notice the apostrophes)
refers to: $C$12 (it can refer to the same
address
or not)

I can refer to those range names on sheet7653 by:
=sheet1!ABC+'sheet 99'!ABC

And it'll work fine.



Roger Govier wrote:

Hi Epinn
Can someone confirm this is not possible please?

That is correct.
You can't have the same name for 2 different ranges, either on
the
same
sheet or different sheets.

You can have the same range on multiple sheets adopt the same
name
provided there is only an exclamation "!" in front of the
range,
not
Sheet!

--
Regards

Roger Govier

"Epinn" wrote in message
...
Thank you all for your response. What I want is very simple,
nothing
to
do with VBA.

Basically, I was trying to have the *same name* for A1:A10 on
two
sheets
(within the same workbook) respectively. Jim and Biff gave me
the
answer.

I don't think I can name A1:A10 on Sheet1 as "ABC" and then also
name
B1:B10 on Sheet2 as "ABC" as well. I used insertnamedefine
and
I
also
use the Sheet# to qualify the range, but no luck.

Can someone confirm this is not possible please?

The Name Manager may come in handy one of these days. Thanks
Dave.

Epinn

"Epinn" wrote in message
...
Note: This is a continuation of a previous discussion which you
don't
really need.

Dave,

I did an experiment. I opened a brand new workbook. On Sheet1,
I
selected A1:A10 and defined it with a name "Range123." Then, on
Sheet2,
I did the same. As soon as I did that, "Range123" from Sheet1
no
longer
existed. So, I don't really understand how the user can have
the
same
named ranges within one workbook? You know what, I even took
out
the
reference to Sheet and tried to force in just the cell
reference;
but
Excel won't let me. What am I missing here? Wonder if *two*
workbooks
are involved when we talk about "same name?"

Sounds like you were talking about VBA. I didn't pick this up.

Look forward to your comments.

Epinn

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Named Ranges
 

And just to add to Roger's response.

Create a new workbook with two worksheets: Name them Sheet1 and Sheet2.

In A1 of Sheet1, put this formula:
=sheet2!a1

Now rename sheet2 a few times. Name it "Sheet 2", name it "Sheet 99", name it
"1234", name it "Epinn's Sheet"

Look at the formula after each rename.

Epinn wrote:

Finally, I think the light bulb is ___! Glad Roger understood my question and that the dialogue between Dave and Roger helped me sort out a few things. Yes, how do we define "same name?" I'll leave it for another day. The important thing here is I am quite clear now on how to define names.



Yesterday, I said, "Jim and Biff gave me the answer." Today, I said, "Dave and Pete gave me the answer yesterday as well." Sorry for the delay. It took me a while to grasp what you were trying to tell me. I did read about qualifying the range name by the sheet. Unfortunately, I had a different frame of reference. I focused on the bottom part of the "define name" dialogue box which is "Refers to" while you referred to the top part of the dialogue box "Names in workbook." I didn't know that I had to actually key in "Sheet2!" as part of the name. This is why I told you I had no luck using the same name via insertnamedefine etc.



Now I have a question on apostrophes.



If I do ctrl+drag of a sheet tab that contains a named range and if I *rename* the duplicate (new) sheet, (e.g. newcopy) then there won't be apostrophes around the sheet name (newcopy) in the define name dialogue box. But if I don't rename the duplicate sheet, say I keep sheet 1(2) as the name, then there will be apostrophes around the sheet name in the define name dialogue box; e.g. 'sheet 1(2)'



I find this confusing.



Dave, you included the following in your posts.



=sheet1!ABC+'sheet 99'!ABC



'sheet 99'!ABC (notice the apostrophes)



Yes, I pay attention to the apostrophes but I don't understand why sheet99 has apostrophes and sheet1 doesn't. When I include the sheet name without apostrophes, everything seems to work fine as well. Do I really need apostrophes? I am missing something here.



Please help.



Epinn

"Dave Peterson" wrote in message ...
I think you're in the minority here.

But it does prove that it's good to specify what you mean if there's any doubt.

Roger Govier wrote:

do they share the same name?

Not if they rely on Sheet(n)! in front of them to operate, IMO.

--
Regards

Roger Govier

"Dave Peterson" wrote in message
...
I guess it boils down to what you consider the name.

If you create a worksheet level name on Sheet1 named ABC and another
worksheet
level name on Sheet2 also named ABC, do they share the same name?



Roger Govier wrote:

Hi Dave

Quite right, but in my eyes those are different named ranges.
Epinn was asking about the same name - which is what I was
answering -
and in that case you cannot use the same name to point to 2 different
ranges.

--
Regards

Roger Govier

"Dave Peterson" wrote in message
...
If you include the sheet name in the definition, you can have sheet
level names
that all use that same ABC name.

While on Sheet1:
Insert Name|Define
Names in workbook: sheet1!ABC
refers to: $a$1

While on Sheet 99 (notice the space):
Insert Name|Define
Names in workbook: 'sheet 99'!ABC (notice the apostrophes)
refers to: $C$12 (it can refer to the same
address
or not)

I can refer to those range names on sheet7653 by:
=sheet1!ABC+'sheet 99'!ABC

And it'll work fine.



Roger Govier wrote:

Hi Epinn
Can someone confirm this is not possible please?

That is correct.
You can't have the same name for 2 different ranges, either on the
same
sheet or different sheets.

You can have the same range on multiple sheets adopt the same name
provided there is only an exclamation "!" in front of the range,
not
Sheet!

--
Regards

Roger Govier

"Epinn" wrote in message
...
Thank you all for your response. What I want is very simple,
nothing
to
do with VBA.

Basically, I was trying to have the *same name* for A1:A10 on two
sheets
(within the same workbook) respectively. Jim and Biff gave me the
answer.

I don't think I can name A1:A10 on Sheet1 as "ABC" and then also
name
B1:B10 on Sheet2 as "ABC" as well. I used insertnamedefine and
I
also
use the Sheet# to qualify the range, but no luck.

Can someone confirm this is not possible please?

The Name Manager may come in handy one of these days. Thanks
Dave.

Epinn

"Epinn" wrote in message
...
Note: This is a continuation of a previous discussion which you
don't
really need.

Dave,

I did an experiment. I opened a brand new workbook. On Sheet1, I
selected A1:A10 and defined it with a name "Range123." Then, on
Sheet2,
I did the same. As soon as I did that, "Range123" from Sheet1 no
longer
existed. So, I don't really understand how the user can have the
same
named ranges within one workbook? You know what, I even took out
the
reference to Sheet and tried to force in just the cell reference;
but
Excel won't let me. What am I missing here? Wonder if *two*
workbooks
are involved when we talk about "same name?"

Sounds like you were talking about VBA. I didn't pick this up.

Look forward to your comments.

Epinn

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Named Ranges - my discovery
 

Thank you all for your input and guidance. Special thanks to Dave and Roger who help switch on the light. Glad Roger appears to know how my brain is wired; Boolean is a good example. I really needed his explanation on apostrophe. Last but not least, I found Dave's suggested experiment inspiring.

I made a discovery with the experiment. There is another situation (other than having *spaces* in the sheet name) that Excel will insert the apostrophes. Do you want to guess what it is before you read on?

Excel doesn't allow the following characters as part of the sheet name.

: ? / \[ ] *



But it allows exclamation mark(s) i.e. ! as part of a sheet name.



If I name my sheet



! or dupcopy! or my!!!copy



Excel will include the apostrophes even though there are *no* spaces. It makes sense. Can you imagine if there are no ' ', but on the other hand, users probably won't use !!! in a sheet name; only someone like me in a testing environment ...... Please stay tuned for more discoveries in the future. ;)



Thanks again. It has been educational. If Dave remembers the details of the problem of !$A$1 in the future, I am sure we are interested.



I think I can put this thread to bed now. zzzzzzz You are welcome to continue to contribute. (A few bed time stories won't hurt.)



Epinn














"Dave Peterson" wrote in message ...
And just to add to Roger's response.

Create a new workbook with two worksheets: Name them Sheet1 and Sheet2.

In A1 of Sheet1, put this formula:
=sheet2!a1

Now rename sheet2 a few times. Name it "Sheet 2", name it "Sheet 99", name it
"1234", name it "Epinn's Sheet"

Look at the formula after each rename.

Epinn wrote:

Finally, I think the light bulb is ___! Glad Roger understood my question and that the dialogue between Dave and Roger helped me sort out a few things. Yes, how do we define "same name?" I'll leave it for another day. The important thing here is I am quite clear now on how to define names.



Yesterday, I said, "Jim and Biff gave me the answer." Today, I said, "Dave and Pete gave me the answer yesterday as well." Sorry for the delay. It took me a while to grasp what you were trying to tell me. I did read about qualifying the range name by the sheet. Unfortunately, I had a different frame of reference. I focused on the bottom part of the "define name" dialogue box which is "Refers to" while you referred to the top part of the dialogue box "Names in workbook." I didn't know that I had to actually key in "Sheet2!" as part of the name. This is why I told you I had no luck using the same name via insertnamedefine etc.



Now I have a question on apostrophes.



If I do ctrl+drag of a sheet tab that contains a named range and if I *rename* the duplicate (new) sheet, (e.g. newcopy) then there won't be apostrophes around the sheet name (newcopy) in the define name dialogue box. But if I don't rename the duplicate sheet, say I keep sheet 1(2) as the name, then there will be apostrophes around the sheet name in the define name dialogue box; e.g. 'sheet 1(2)'



I find this confusing.



Dave, you included the following in your posts.



=sheet1!ABC+'sheet 99'!ABC



'sheet 99'!ABC (notice the apostrophes)



Yes, I pay attention to the apostrophes but I don't understand why sheet99 has apostrophes and sheet1 doesn't. When I include the sheet name without apostrophes, everything seems to work fine as well. Do I really need apostrophes? I am missing something here.



Please help.



Epinn

"Dave Peterson" wrote in message ...
I think you're in the minority here.

But it does prove that it's good to specify what you mean if there's any doubt.

Roger Govier wrote:

do they share the same name?

Not if they rely on Sheet(n)! in front of them to operate, IMO.

--
Regards

Roger Govier

"Dave Peterson" wrote in message
...
I guess it boils down to what you consider the name.

If you create a worksheet level name on Sheet1 named ABC and another
worksheet
level name on Sheet2 also named ABC, do they share the same name?



Roger Govier wrote:

Hi Dave

Quite right, but in my eyes those are different named ranges.
Epinn was asking about the same name - which is what I was
answering -
and in that case you cannot use the same name to point to 2 different
ranges.

--
Regards

Roger Govier

"Dave Peterson" wrote in message
...
If you include the sheet name in the definition, you can have sheet
level names
that all use that same ABC name.

While on Sheet1:
Insert Name|Define
Names in workbook: sheet1!ABC
refers to: $a$1

While on Sheet 99 (notice the space):
Insert Name|Define
Names in workbook: 'sheet 99'!ABC (notice the apostrophes)
refers to: $C$12 (it can refer to the same
address
or not)

I can refer to those range names on sheet7653 by:
=sheet1!ABC+'sheet 99'!ABC

And it'll work fine.



Roger Govier wrote:

Hi Epinn
Can someone confirm this is not possible please?

That is correct.
You can't have the same name for 2 different ranges, either on the
same
sheet or different sheets.

You can have the same range on multiple sheets adopt the same name
provided there is only an exclamation "!" in front of the range,
not
Sheet!

--
Regards

Roger Govier

"Epinn" wrote in message
...
Thank you all for your response. What I want is very simple,
nothing
to
do with VBA.

Basically, I was trying to have the *same name* for A1:A10 on two
sheets
(within the same workbook) respectively. Jim and Biff gave me the
answer.

I don't think I can name A1:A10 on Sheet1 as "ABC" and then also
name
B1:B10 on Sheet2 as "ABC" as well. I used insertnamedefine and
I
also
use the Sheet# to qualify the range, but no luck.

Can someone confirm this is not possible please?

The Name Manager may come in handy one of these days. Thanks
Dave.

Epinn

"Epinn" wrote in message
...
Note: This is a continuation of a previous discussion which you
don't
really need.

Dave,

I did an experiment. I opened a brand new workbook. On Sheet1, I
selected A1:A10 and defined it with a name "Range123." Then, on
Sheet2,
I did the same. As soon as I did that, "Range123" from Sheet1 no
longer
existed. So, I don't really understand how the user can have the
same
named ranges within one workbook? You know what, I even took out
the
reference to Sheet and tried to force in just the cell reference;
but
Excel won't let me. What am I missing here? Wonder if *two*
workbooks
are involved when we talk about "same name?"

Sounds like you were talking about VBA. I didn't pick this up.

Look forward to your comments.

Epinn

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Named Ranges - my discovery
 

Just this portion:

If Dave remembers the details of the problem of !$A$1 in the future, I am sure
we are interested.

I can't imagine that happening <bg. But I can imagine someone posting the
reason independent of my memory!

Epinn wrote:

Thank you all for your input and guidance. Special thanks to Dave and Roger who help switch on the light. Glad Roger appears to know how my brain is wired; Boolean is a good example. I really needed his explanation on apostrophe. Last but not least, I found Dave's suggested experiment inspiring.

I made a discovery with the experiment. There is another situation (other than having *spaces* in the sheet name) that Excel will insert the apostrophes. Do you want to guess what it is before you read on?

Excel doesn't allow the following characters as part of the sheet name.

: ? / \[ ] *

But it allows exclamation mark(s) i.e. ! as part of a sheet name.

If I name my sheet

! or dupcopy! or my!!!copy

Excel will include the apostrophes even though there are *no* spaces. It makes sense. Can you imagine if there are no ' ', but on the other hand, users probably won't use !!! in a sheet name; only someone like me in a testing environment ...... Please stay tuned for more discoveries in the future. ;)

Thanks again. It has been educational. If Dave remembers the details of the problem of !$A$1 in the future, I am sure we are interested.

I think I can put this thread to bed now. zzzzzzz You are welcome to continue to contribute. (A few bed time stories won't hurt.)

Epinn






"Dave Peterson" wrote in message ...
And just to add to Roger's response.

Create a new workbook with two worksheets: Name them Sheet1 and Sheet2.

In A1 of Sheet1, put this formula:
=sheet2!a1

Now rename sheet2 a few times. Name it "Sheet 2", name it "Sheet 99", name it
"1234", name it "Epinn's Sheet"

Look at the formula after each rename.

Epinn wrote:

Finally, I think the light bulb is ___! Glad Roger understood my question and that the dialogue between Dave and Roger helped me sort out a few things. Yes, how do we define "same name?" I'll leave it for another day. The important thing here is I am quite clear now on how to define names.



Yesterday, I said, "Jim and Biff gave me the answer." Today, I said, "Dave and Pete gave me the answer yesterday as well." Sorry for the delay. It took me a while to grasp what you were trying to tell me. I did read about qualifying the range name by the sheet. Unfortunately, I had a different frame of reference. I focused on the bottom part of the "define name" dialogue box which is "Refers to" while you referred to the top part of the dialogue box "Names in workbook." I didn't know that I had to actually key in "Sheet2!" as part of the name. This is why I told you I had no luck using the same name via insertnamedefine etc.



Now I have a question on apostrophes.



If I do ctrl+drag of a sheet tab that contains a named range and if I *rename* the duplicate (new) sheet, (e.g. newcopy) then there won't be apostrophes around the sheet name (newcopy) in the define name dialogue box. But if I don't rename the duplicate sheet, say I keep sheet 1(2) as the name, then there will be apostrophes around the sheet name in the define name dialogue box; e.g. 'sheet 1(2)'



I find this confusing.



Dave, you included the following in your posts.



=sheet1!ABC+'sheet 99'!ABC



'sheet 99'!ABC (notice the apostrophes)



Yes, I pay attention to the apostrophes but I don't understand why sheet99 has apostrophes and sheet1 doesn't. When I include the sheet name without apostrophes, everything seems to work fine as well. Do I really need apostrophes? I am missing something here.



Please help.



Epinn

"Dave Peterson" wrote in message ...
I think you're in the minority here.

But it does prove that it's good to specify what you mean if there's any doubt.

Roger Govier wrote:

do they share the same name?

Not if they rely on Sheet(n)! in front of them to operate, IMO.

--
Regards

Roger Govier

"Dave Peterson" wrote in message
...
I guess it boils down to what you consider the name.

If you create a worksheet level name on Sheet1 named ABC and another
worksheet
level name on Sheet2 also named ABC, do they share the same name?



Roger Govier wrote:

Hi Dave

Quite right, but in my eyes those are different named ranges.
Epinn was asking about the same name - which is what I was
answering -
and in that case you cannot use the same name to point to 2 different
ranges.

--
Regards

Roger Govier

"Dave Peterson" wrote in message
...
If you include the sheet name in the definition, you can have sheet
level names
that all use that same ABC name.

While on Sheet1:
Insert Name|Define
Names in workbook: sheet1!ABC
refers to: $a$1

While on Sheet 99 (notice the space):
Insert Name|Define
Names in workbook: 'sheet 99'!ABC (notice the apostrophes)
refers to: $C$12 (it can refer to the same
address
or not)

I can refer to those range names on sheet7653 by:
=sheet1!ABC+'sheet 99'!ABC

And it'll work fine.



Roger Govier wrote:

Hi Epinn
Can someone confirm this is not possible please?

That is correct.
You can't have the same name for 2 different ranges, either on the
same
sheet or different sheets.

You can have the same range on multiple sheets adopt the same name
provided there is only an exclamation "!" in front of the range,
not
Sheet!

--
Regards

Roger Govier

"Epinn" wrote in message
...
Thank you all for your response. What I want is very simple,
nothing
to
do with VBA.

Basically, I was trying to have the *same name* for A1:A10 on two
sheets
(within the same workbook) respectively. Jim and Biff gave me the
answer.

I don't think I can name A1:A10 on Sheet1 as "ABC" and then also
name
B1:B10 on Sheet2 as "ABC" as well. I used insertnamedefine and
I
also
use the Sheet# to qualify the range, but no luck.

Can someone confirm this is not possible please?

The Name Manager may come in handy one of these days. Thanks
Dave.

Epinn

"Epinn" wrote in message
...
Note: This is a continuation of a previous discussion which you
don't
really need.

Dave,

I did an experiment. I opened a brand new workbook. On Sheet1, I
selected A1:A10 and defined it with a name "Range123." Then, on
Sheet2,
I did the same. As soon as I did that, "Range123" from Sheet1 no
longer
existed. So, I don't really understand how the user can have the
same
named ranges within one workbook? You know what, I even took out
the
reference to Sheet and tried to force in just the cell reference;
but
Excel won't let me. What am I missing here? Wonder if *two*
workbooks
are involved when we talk about "same name?"

Sounds like you were talking about VBA. I didn't pick this up.

Look forward to your comments.

Epinn

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Named Ranges
 

Thanks to (Mr.?) Ragdyer.

************************************************** ************************
The main purpose of " =!A1 ", was to create a WS specific name, and to
*globally* (concurrently) assign it to *all* the existing sheets in the WB.

The shortcoming of this naming technique is that it *doesn't* recalculate.
Changing the value in the named cell does *not* force a formula containing
this name to auto-recalculate. It makes it appear as if the sheet is set to
manual calculation, where the formula containing cell must be individually
re-entered in the formula bar.

A naming procedure that *does* work to globally assign WS specific names to
*all* existing sheets is:

=INDIRECT("A1")

************************************************** ****************************
Epinn

"Dave Peterson" wrote in message ...
Just this portion:

If Dave remembers the details of the problem of !$A$1 in the future, I am sure
we are interested.

I can't imagine that happening <bg. But I can imagine someone posting the
reason independent of my memory!

Epinn wrote:

Thank you all for your input and guidance. Special thanks to Dave and Roger who help switch on the light. Glad Roger appears to know how my brain is wired; Boolean is a good example. I really needed his explanation on apostrophe. Last but not least, I found Dave's suggested experiment inspiring.

I made a discovery with the experiment. There is another situation (other than having *spaces* in the sheet name) that Excel will insert the apostrophes. Do you want to guess what it is before you read on?

Excel doesn't allow the following characters as part of the sheet name.

: ? / \[ ] *

But it allows exclamation mark(s) i.e. ! as part of a sheet name.

If I name my sheet

! or dupcopy! or my!!!copy

Excel will include the apostrophes even though there are *no* spaces. It makes sense. Can you imagine if there are no ' ', but on the other hand, users probably won't use !!! in a sheet name; only someone like me in a testing environment ...... Please stay tuned for more discoveries in the future. ;)

Thanks again. It has been educational. If Dave remembers the details of the problem of !$A$1 in the future, I am sure we are interested.

I think I can put this thread to bed now. zzzzzzz You are welcome to continue to contribute. (A few bed time stories won't hurt.)

Epinn






"Dave Peterson" wrote in message ...
And just to add to Roger's response.

Create a new workbook with two worksheets: Name them Sheet1 and Sheet2.

In A1 of Sheet1, put this formula:
=sheet2!a1

Now rename sheet2 a few times. Name it "Sheet 2", name it "Sheet 99", name it
"1234", name it "Epinn's Sheet"

Look at the formula after each rename.

Epinn wrote:

Finally, I think the light bulb is ___! Glad Roger understood my question and that the dialogue between Dave and Roger helped me sort out a few things. Yes, how do we define "same name?" I'll leave it for another day. The important thing here is I am quite clear now on how to define names.



Yesterday, I said, "Jim and Biff gave me the answer." Today, I said, "Dave and Pete gave me the answer yesterday as well." Sorry for the delay. It took me a while to grasp what you were trying to tell me. I did read about qualifying the range name by the sheet. Unfortunately, I had a different frame of reference. I focused on the bottom part of the "define name" dialogue box which is "Refers to" while you referred to the top part of the dialogue box "Names in workbook." I didn't know that I had to actually key in "Sheet2!" as part of the name. This is why I told you I had no luck using the same name via insertnamedefine etc.



Now I have a question on apostrophes.



If I do ctrl+drag of a sheet tab that contains a named range and if I *rename* the duplicate (new) sheet, (e.g. newcopy) then there won't be apostrophes around the sheet name (newcopy) in the define name dialogue box. But if I don't rename the duplicate sheet, say I keep sheet 1(2) as the name, then there will be apostrophes around the sheet name in the define name dialogue box; e.g. 'sheet 1(2)'



I find this confusing.



Dave, you included the following in your posts.



=sheet1!ABC+'sheet 99'!ABC



'sheet 99'!ABC (notice the apostrophes)



Yes, I pay attention to the apostrophes but I don't understand why sheet99 has apostrophes and sheet1 doesn't. When I include the sheet name without apostrophes, everything seems to work fine as well. Do I really need apostrophes? I am missing something here.



Please help.



Epinn

"Dave Peterson" wrote in message ...
I think you're in the minority here.

But it does prove that it's good to specify what you mean if there's any doubt.

Roger Govier wrote:

do they share the same name?

Not if they rely on Sheet(n)! in front of them to operate, IMO.

--
Regards

Roger Govier

"Dave Peterson" wrote in message
...
I guess it boils down to what you consider the name.

If you create a worksheet level name on Sheet1 named ABC and another
worksheet
level name on Sheet2 also named ABC, do they share the same name?



Roger Govier wrote:

Hi Dave

Quite right, but in my eyes those are different named ranges.
Epinn was asking about the same name - which is what I was
answering -
and in that case you cannot use the same name to point to 2 different
ranges.

--
Regards

Roger Govier

"Dave Peterson" wrote in message
...
If you include the sheet name in the definition, you can have sheet
level names
that all use that same ABC name.

While on Sheet1:
Insert Name|Define
Names in workbook: sheet1!ABC
refers to: $a$1

While on Sheet 99 (notice the space):
Insert Name|Define
Names in workbook: 'sheet 99'!ABC (notice the apostrophes)
refers to: $C$12 (it can refer to the same
address
or not)

I can refer to those range names on sheet7653 by:
=sheet1!ABC+'sheet 99'!ABC

And it'll work fine.



Roger Govier wrote:

Hi Epinn
Can someone confirm this is not possible please?

That is correct.
You can't have the same name for 2 different ranges, either on the
same
sheet or different sheets.

You can have the same range on multiple sheets adopt the same name
provided there is only an exclamation "!" in front of the range,
not
Sheet!

--
Regards

Roger Govier

"Epinn" wrote in message
...
Thank you all for your response. What I want is very simple,
nothing
to
do with VBA.

Basically, I was trying to have the *same name* for A1:A10 on two
sheets
(within the same workbook) respectively. Jim and Biff gave me the
answer.

I don't think I can name A1:A10 on Sheet1 as "ABC" and then also
name
B1:B10 on Sheet2 as "ABC" as well. I used insertnamedefine and
I
also
use the Sheet# to qualify the range, but no luck.

Can someone confirm this is not possible please?

The Name Manager may come in handy one of these days. Thanks
Dave.

Epinn

"Epinn" wrote in message
...
Note: This is a continuation of a previous discussion which you
don't
really need.

Dave,

I did an experiment. I opened a brand new workbook. On Sheet1, I
selected A1:A10 and defined it with a name "Range123." Then, on
Sheet2,
I did the same. As soon as I did that, "Range123" from Sheet1 no
longer
existed. So, I don't really understand how the user can have the
same
named ranges within one workbook? You know what, I even took out
the
reference to Sheet and tried to force in just the cell reference;
but
Excel won't let me. What am I missing here? Wonder if *two*
workbooks
are involved when we talk about "same name?"

Sounds like you were talking about VBA. I didn't pick this up.

Look forward to your comments.

Epinn

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson