reference sheet number not name

  • Thread starter Thread starter Al
  • Start date Start date
A

Al

Hello
Is there a way to reference a sheet name based on its number rather than its
name?
Thanks
 
First enter the following User Defined Function:

Function SheetName(i As Integer) As String
SheetName = Sheets(i).Name
End Function

Then in some cell enter:

=INDIRECT(sheetname(2) & "!B9") to get the value of cell B9 in the second
sheet.

UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel Function. For example =SheetName(3)

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx
 
Back
Top