(Name) vs. "Name"

roemun

New Member
Joined
Aug 6, 2009
Messages
23
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?
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,613
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.
Code:
MsgBox Sheet1.Range("A1").Value
MsgBox Sheets("SheetAlpha").Range("A1").Value
 

roemun

New Member
Joined
Aug 6, 2009
Messages
23
I understand. I was looking for a way to use that reference in a formula.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,668
Office Version
2013
Platform
Windows
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

New Member
Joined
Aug 6, 2009
Messages
23
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,668
Office Version
2013
Platform
Windows
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

New Member
Joined
Aug 6, 2009
Messages
23
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

Legend
Joined
Jun 19, 2002
Messages
63,651
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,668
Office Version
2013
Platform
Windows
Right - Excel keeps everything up to snuff for you, no worries.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,805
Messages
5,482,994
Members
407,371
Latest member
Ernest F Mink

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top