Referencing a sheet using the sheet name

[orion_wa]

a quick problem. im not too sure how to do this
anyways.. i'll give you a quick example of what im trying to do.

okay i want to make some quick formulas but i dont want to use the sheet name like it normally does. and i need to make it function while cells are protected so it cant be a vb script. okay for short.

you have a sheet name.. then you have its actual name.
one is visible.. and the other is only visible when you go into properties.

anyways the visible sheet name needs to be loose as it will potentially change with some of my other scripts. so when i reference a cell in vb i use the actual sheet name instead of the visible name.
now all i need to know is how i can reference the actual name in a excell script.. like for example
with the visible name i would call something from the sheet using somethin like

=sheet1!C1R1

does anyone know how i can do it using the properties name ?

any help would be helpful
ta

 

[mikerickson]

I think you are talking about the .CodeName vs. the .Name properties.

Sheets("NameOnTab")

uses the .Name property of a sheet to refer to it.

This function

Function SheetFromCodeName(codeNameString As String) As  Worksheet 
    On  Error Resume Next 
    With  ThisWorkbook.VBProject.vbcomponents(codeNameString) 
        Set SheetFromCodeName = ThisWorkbook.Sheets(. properties("index").Value) 
    End With 
    On Error Goto 0 
End Function

will return the sheet when given its code name.

 

[orion_wa]

umm.. your confusing me.. i dont want to get the name.

i want to do an excel script using the properties codename rather then tab name.

so in a cell i want to make a forumula referencing the sheet using the codename rather then the sheet tabname
does that make more sence ?

 

[mikerickson]

Let me add another UDF.

Function TabNameFromCodeName(codeNameString as String) as String
    tabNameFromCodeName = SheetFromCodeName(codeNameString).Name
End Function

Putting both UDF's in a module will permit spreadsheet functions like

=SUM(INDIRECT(TabNameFromCodeName("codeName") & "!A1:A10"))