Link Sheet name to a cell

[Tmoske]

Is it possible to link a sheetname?
I have a list of employees on sheet1 and each employee has there own sheet with their name as the sheet name. Each sheet contains a form that the employees need to fill in.

I have created several extra sheets for new employees and I would like these sheet names to be linked to a cell on sheet1. This way the only updating I need to do when receiving new employees is enter there name to the list in sheet1.

Thanks,
Tmoske

 

[FormR]

Is it possible to link a sheetname?
I have a list of employees on sheet1 and each employee has there own sheet with their name as the sheet name. Each sheet contains a form that the employees need to fill in.

I have created several extra sheets for new employees and I would like these sheet names to be linked to a cell on sheet1. This way the only updating I need to do when receiving new employees is enter there name to the list in sheet1.

Thanks,
Tmoske

Do you mean create hyperlinks to the other sheets?

Excel Workbook
	A	B
1	Name	Link
2	John	John
3	Jack	Jack
Sheet1

 

[Tmoske]

No, what I want is to link the sheetname to a specific cell. Whenever you change the data in the cells it updates the sheet name.

Thanks,
Tmoske

 

[andrewkard]

Hello, maybe so:
=HYPERLINK("#John!A1","John")
But it is the same.

 

[skipres]

I believe that the only way to change the Sheetname is with a macro - not a formula.

 

[andrewkard]

Hello skipres, try the code below on the copy of your workbook, it renames worksheets with your list of names.

Sub ReNameSheets()
Dim i%, l%, n%, max%, rRg As Range, aList()
Set rRg = Application.InputBox("Select list of names", Type:=8)
n = 0
max = rRg.Rows.Count * rRg.Columns.Count
ReDim aList(0 To max)
For i = rRg.Row To rRg.Row + rRg.Rows.Count - 1
For l = rRg.Column To rRg.Column + rRg.Columns.Count - 1
aList(n) = Cells(i, l)
n = n + 1
Next l
Next i
i = Application.InputBox("Enter a number of sheet, from which we will rename sheets") + 1
n = 0
For i = i To Sheets.Count
On Error Resume Next
Worksheets(i).Name = aList(n)
If Err.Number = 1004 Then
MsgBox ("The name " & aList(n) & " is already used!")
Exit Sub
End If
n = n + 1
Next i
End Sub

 

[skipres]

Thanks, I can think of a few applications where this macro might come in handy, but, like I mentioned in my post, the sheetname cannot be changed with just a formula, you would still have to invoke a macro to grab the information, locate the worksheet, and rename it.

I gathered (maybe mistakenly) that the author wanted the sheetname to be linked to a cell - so that the name would change when the value in the cell changed. When you right click on a Tab and select Rename, it would be great if you could enter a formula directly as the Tab Name.

Thanks again for the macro, I am always referring back to my collection.

 

[andrewkard]

Skipres, sorry, my message was to the author, not you . I did mistake.