(Name) vs. "Name"

[roemun]

Is there a way to refer to a worksheet in a formula by its (name) as opposed to its "name", (name) being what is shown as the sheet's first property in the properties window? Or maybe a function?

 

[xenou]

You may find this post from erik.van.geit helpful:
http://www.mrexcel.com/forum/showthread.php?t=253546&highlight=worksheets

Alex

 

[mikerickson]

It sounds like you are refering to the CodeName property of a sheet.
If in the Project Explorer window shows "Sheet1 (SheetAlpha)" then
"Sheet1" is the CodeName of the sheet, "SheetAlpha" is the Name of the sheet (which is shown on the tab. Both of these message boxes will show the same result.

MsgBox Sheet1.Range("A1").Value
MsgBox Sheets("SheetAlpha").Range("A1").Value

 

[roemun]

I understand. I was looking for a way to use that reference in a formula.

 

[xenou]

Can you give an example of the formula you want to use?

Generally, in Visual Basic macro code you have more options. But you might find Defined Names convenient in your worksheet formulas, depending on what your aim is.

Alex

 

[roemun]

Sample formula:

='shtname'!$D89

where I want to substitute the codename for the sheet, e.g. Sheet1, in place of the user defined sheet name. in the above "shtname"

 

[xenou]

You don't gain anything from this - Use the sheet names on the tab. That's what they are for A UDF could be devised but you'd have inferior performance.

 

[roemun]

What I am trying to "gain from this" is that I want the formula in that cell to remain effective regardless of what the tab name for the sheet might be. If there is code that would accomplish this, I would be happy to use it, or, as you suggest, a UDF. I am having difficulty devising one or the other of these.

 

[VoG]

Try this.

Name a sheet Smith (or anything you want). Enter anything in A1.

On another sheet enter

=Smith!A1

Now change the name of sheet Smith. What happens?

 

[xenou]

Right - Excel keeps everything up to snuff for you, no worries.