MrExcel.com - Your One Stop for Excel Tips & Solutions

Naming a Worksheet as the Value of a Target Cell

October 16, 2001 - by

David asks:

How can I change the name of the worksheet only, with text located in cell A1.

Use this macro: 

Public Sub RenameSheet()
	NewName = Range("A1").Value
	ActiveSheet.Name = NewName
End Sub

Chris had downloaded the free Microsoft add-in to find and remove links to other workbooks. It worked fine, but then he started getting an error message: "Routine Location Error Code: 2". I could not find anything at the Microsoft site, but a German-language Excel message board mentioned a similar problem. Without an answer from Microsoft, it is hard to say why this happens on some computers and not others. If you find that you are experiencing this problem, you should uninstall the add-in on the Tools > Add-ins dialog.

Main wrote to ask if there is a keyboard shortcut for moving to other tabs in a workbook. I always use Ctrl + PgUp to move to a tab to the left of the current worksheet, and Ctrl + Pgdn to move to a tab to the right.

Paul asks:

I've got for example the following link: =Sheet2!$B$4. Is it possible to make the sheetname (Sheet2) variable e.g. referring to a cell with the name of the sheet?

Paul - the =INDIRECT() function should help you in this regard. Anywhere that you would normally type a cell reference, you can use the Indirect function. Inside the parentheses, you can use concatenation characters to build a valid cell reference. For example, in cell E4, I typed "Sheet2". In another cell, I typed this formula:

=INDIRECT(E4&"!B4")

The formula correctly returned the contents of cell B4 on Sheet2. The downside of using this method is that since the argument B4 is text, Excel will not update this formula if you ever cut and paste B4 to a new location.

Update Dec 2003:

Thanks to Sai Prasad who wrote with an improvement to this formula which can be copied:

=INDIRECT($A$2&"!"&ADDRESS(ROW(),COLUMN()))

Update March 2005

Thanks to Francisco Sandoval who wrote with this improvement: 

=INDIRECT(ADDRESS(ROW(),COLUMN(),4,1,$A$2)) 

Where the 3rd variable in this case a 4, refers to how the cell will be referenced (absolute, relative, etc.) and the 4th varialbe refers to (A1 vs L1C1)

That's all for today - I've got a lot of consulting work to get done today. Sorry to all who I did not have time to answer. If you did not see your question answered, try posting it to the message board - many loyal readers help with questions on the board - it is a great way to get answers quickly.