reference sheet number not name

A

Al

Hello
Is there a way to reference a sheet name based on its number rather than its
name?
Thanks
 
G

Gary''s Student

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Match and delete rows on two different sheets from reference numbers 4
Re-title multiple MS Project .mpp files at once 0
Macro to select and delete levels of a document 2
Possible to Combine reports to's to a single box? 0
Excell Otpions in office environment 0
Excel does not accept text format! 0
Insert name and adress from a list in a word document 1
Section and Page numbering 0

Top