Relative Sheet

[Martin Schmid]

In excel 2000, is it possible to reference to a relaive sheet? I.e., I have
sheets 1,2,3,4,5 I want 2 to reference a cell on 1, 3 to ref a cell on 2,
etc... w/ a simple function.

Thanks,

 

[dvt]

In excel 2000, is it possible to reference to a relaive sheet? I.e.,
I have sheets 1,2,3,4,5 I want 2 to reference a cell on 1, 3 to ref
a cell on 2, etc... w/ a simple function.

Thanks,

=Sheet1!A1 refers to cell A1 on sheet 1.

Dave
dvt at psu dot edu

 

[Martin Schmid]

I know that much... but what I want to do is have the 'Sheet1' refer to the
previous sheet in the tab order, not necessarily hard reference
'Sheet1','Sheet2', etc.

 

[Bernie Deitrick]

Martin,

There is no relative reference for sheets. The best you can do is a
User-Defined-Function, used like

=PrevSheet("A1")

Here's the definition: put it into a regular codemodule in your
workbook.

Function PrevSheet(inCell As String) As Variant
Dim myNum As Integer
myNum = Application.Caller.Parent.Index

On Error Resume Next
PrevSheet = Worksheets(myNum - 1).Range(inCell).Value
End Function

HTH,
Bernie

 

[immanuel]

Have you considered using INDIRECT()? You might be able to do something
like:

=INDIRECT("Sheet" & A1 & "!A2")

You could put the sheet number to reference in A1 on each sheet and the
above should work for you...

Interesting problem. Post if you find a more parsimonious solution.

/i.