Named Ranges

  • Thread starter Thread starter Epinn
  • Start date Start date
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.
 
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














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.
 
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!
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






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.
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 insert>name>define 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.






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

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.
Click to expand...
Click to expand...
 
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

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!
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






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.
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 insert>name>define 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.






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

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.
Click to expand...
Click to expand...
 
Back
Top