relative sheet reference [Solved]

[Corticus]

Hi all,

Is there a way to refer to a sheet by number, say sheet1, in a formula, regardless of what the sheet is named?

No luck with search...

Thanks!

 

[Nimrod]

Here's three way to refer to a sheet
Method 1 and 3 does not change when you change the sheet name

Public Sub selectsheet()
Sheet1.Select
Sheets("Sheet1").Select
Sheets(1).Select

End Sub


A sheet in a workbook is normally referred to by specifying the Sheets collection and the name or the index number of the sheet. The following is the syntax of the this command:


Sheets("sheet name")
or Sheets(number)

For example, Sheets("Sheet1") specifies the sheet named Sheet1 in the active workbook. If Sheet1 were the first sheet in a file, then Sheets(1) would also refer to Sheet1 in the active workbook. If the sheets are moved or sheets added or deleted, the number that refers to a particular sheet can change.

To specify a sheet in a different workbook, the statement Workbooks("filename") can be used as the object qualifier. For example:

Workbooks("Personal.Xls").Sheets("Sheet1")

specifies sheet Sheet1 in Personal.Xls, no matter what workbook or sheet is currently active.

The Sheets collection consists of all types of sheets - worksheets, charts, dialogsheets, and Microsoft Excel 4 macro sheets. The collections Worksheets, Charts, Dialogs, Microsoft Excel4MacroSheets can also be used to specify a sheet:


Worksheets("Sheet1")

or Worksheets(1)

The Count property can be used to find out how many of a particular type sheet there are in a file:


MsgBox "There are " & Charts.Count & " chart sheets."

The following macro displays how many worksheets there are in the active file and then displays each one's name and index number.

Sub DisplayWorksheetNamesAndNumber()
Dim num As Integer, I As Integer
'Determine number of worksheets and display message
num = Worksheets.Count
MsgBox "There are " & num & _
" worksheets in this file"

'Display the number and name of each worksheet
For I = 1 To num
MsgBox "Worksheet " & I & " is called " & _
Worksheets(I).Name
Next I
End Sub


You can also use the keyword ActiveSheet to refer to the active sheet. For example if the active sheet is named "Survey Results", the following two statements both refer to this sheet:


Sheets("Survey Results")

or ActiveSheet

 

[Corticus]

Thanks for the detailed response Nimrod,

Without sounding ungrateful, I know how to do this in code. I would like to refer to the sheet number in a formula.

I was thinking a combination of the getinfo() UDF to pull the sheet names to a table, and then indirect() to use those sheetnames in the formulas, but I'm not sure there isn't a better way.

edit: reason for this, I have formulas refering to sheets that have not yet been renamed from Sheet1 to whatever they will be.

Thanks again!

 

[Corticus]

solved (sort-of)

Hi again,

Well my idea for getinfo() and indirect() worked, so I guess that's good:

CCE_Serv_Agreement.xls
	A	B	C	D
1	1	Sheets	Sheets!C3	Alachua!C3
2	2	Data	Data!C3	CitrusCountySupportServices
3	3	Alachua	Alachua!C3	Alachua
4	4	Citrus	Citrus!C3	Citrus
5	5	Columbia	Columbia!C3	Columbia
6	6	Hamilton	Hamilton!C3	Hamilton
7	7	Hernando	Hernando!C3	Hernando
8	8	Lake	Lake!C3	Lake
9	9	Sumter	Sumter!C3	Sumter
10	10	Marion	Marion!C3	Marion
11	11	Bradford	Bradford!C3	Bradford
12	12	Lafayette	Lafayette!C3	Lafayette
13	13	Putnam	Putnam!C3	Putnam
14	14	Suwannee	Suwannee!C3	Suwannee
15	15	Union	Union!C3	Union
16	16	Dixie	Dixie!C3	Dixie
17	17	Gilchrist	Gilchrist!C3	Gilchrist
18	18	Levy	Levy!C3	Levy
Sheets

But I would still like to know if I can do this without VBA (including UDFs), just with a formula.

Thanks!

 

[Corticus]

One more quickie...

Does anybody know how to make this UDF recalculate if the user adds a sheet, or changes the name of one, or really anything that would cause the table above's values to need to be updated?

I have a consolidated data sheet, that pulls all the data from any tabs in the workbook. I want to be able to account for the user changing the tab names, thus indirect() and getinfo().

Thanks again!

 

[Aladin Akyurek]

=SHEETNAME(1)

returns the name of the sheet with the internal index number 1.

 

[Corticus]

Thanks Aladin,

I think Sheetname() might be from an add-in, perhaps morefunc, because I got #name. I would prefer to use the Getinfo() UDF because I can 'attach' the function to the worksheet, which I don't think anybody know how to do with add-ins.

I would really like to see some of the morefunc functions developed as open source UDFs, so I could send them with my spreadsheets to clients and such. Oh well.

Thank you, though!

 

[Corticus]

Solved

Okay,

If you add:

application.volatile
(thanks NateO!)
to beginning of the function code, Excel will force the UDF to recalculate when you change formats, rename sheets, etc. This solved my dillema.

BTW, the getinfo() UDF is here:

http://www.cpearson.com/excel/GetInfo.htm

Thanks for your help!