Referencing Tab Sheets

[evpsa]

Hello,

Is it possible for a Worksheet Tab to reference a specific cell?

Example...

Cell A1 has the text "Hello"

When naming the worksheet tab, I can use some sort of formula to pul
the text from cell A1, giving that tab the name, "Hello"

Any ideas? Non-VBA would be ideal, as most of the computers at m
company will automatically disable macros.

Thanks,
Scot

 

[Ken Wright]

Not without VBA, though you can do it the other way round if that helps at all,
pulling the tab name into cell A1.

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

reproduce exactly as shown, and do not actually put the filename into the
formula. ;-)

 

[evpsa]

Yeah, I've seen it the other way around while searching these boards.

Can anyone post how to do it with VBA though? I'm no where near th
familiarity with the Excel specific VBA stuff as most users on thi
board

 

[Gord Dibben]

Sub SheetName22()
ActiveSheet.Name = Range("c1")
End Sub

Hit ALT + F11 to open the Visual Basic Editor.

CRTL + r to open the Project Explorer.

Find your workbook/project and right-click and Insert>Module.

Copy/paste the code above into that module.

File>Save.

Hit ALT + Q to go back to your Excel active sheet.

When you run the macro, the active sheet will take the name of whatever is in
C1.

Gord Dibben Excel MVP

 

[evpsa]

Thanks for the reply, Gord.

Follow up question to complicate things a bit...

I actually am going to have a range of about 10 items, and I would lik
for those to be the tab names.

Can this type of automatic naming be applied to several worksheets?
And if so, can the worksheets be automatically created based on
template, and named at the same time? (Perhaps like this for th
creation part, http://tinyurl.com/4hsql)

Here is my idea to further explain and illustrate what I'm thinking:

I have one "Control" worksheet where employee information is entered.
Based off of the range of Employee names(A1:A10 or so), I'd like a ne
worksheet to be created, with the employee name as the tab name. I'
like to make this as dynamic and automated as possible, so m
department can use it after I'm gone(and trust me, if it involve
editting formulas later, they wont be able to use it)

But if anyone has some suggestions, wants to take up the codin
challenge, or can link me to relevant information, I'd much appreciat
it