Prev: I have a file that is 4mb. Cleared all formating...still big
Next: Inserting Lines Makes Formula Inactive
From: Al on 28 Jan 2010 15:00
Hello
Is there a way to reference a sheet name based on its number rather than its
name?
Thanks
From: Gary''s Student on 28 Jan 2010 15:27
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

--
Gary''s Student - gsnu201001


"Al" wrote:

> Hello
> Is there a way to reference a sheet name based on its number rather than its
> name?
> Thanks
 | 
Pages: 1
Prev: I have a file that is 4mb. Cleared all formating...still big
Next: Inserting Lines Makes Formula Inactive