Relative sheet reference

[hansiman]

Is there a way to reference sheets not by itn's name but by its
relative position?

1st sheet name = 'Report'
2nd sheet name = 'Data1'
3rd sheet name = 'Data2'

I'd like to refernece data in sheet 'Data2' relatively. Somthing like
Sheet(3)!A4


/Morten

 

[Frank Kabel]

Hi
try
....
dim rng as range
set rng = worksheets(3).range("A4")
msgbox rng.value
....

 

[Arvi Laanemets]

Hi

An UDF fro below returns sheet name from sheet's index (it's order number in
sheet's collection)

Public Function TabByIndex(TabIndex As Integer) As String
Application.Volatile
TabByIndex = Sheets(TabIndex).Name
End Function

Copy the UDF into some VBA-module. Now the formula :
=INDIRECT(TabByIndex(n)&"!A4)
will return value from cell A1 from sheet 'Report', when n=1;
from sheet 'Data1', when n=2;
etc.