Back

About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store
Podcast
Search
Contact
Home

 

 

Past Tip of the Day

 

David asks I read your tip about a macro to save a workbook with a filename found in cell A1. 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.


By Bill Jelen on 16-Oct-2001

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

 

Excel is a registered trademark of the MicrosoftŪ Corporation.

All contents Copyright 1998-2008 by MrExcel Consulting.