Relative Sheet

  • Thread starter Thread starter Martin Schmid
  • Start date Start date
M

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,
 
Martin said:
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,
Click to expand...

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

Dave
dvt at psu dot edu
 
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.
 
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
 
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.
 
Back
Top