How do I change the sheet a formula refers automatically?

Here's a User Defined Function to store in a General Module.

Function PrevSheet(rg As Range)
'Enter =PrevSheet(B2) on sheet2 and you'll get B2 from sheet1.
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function

Group sheet2 through sheetx and in A1(or your choice) enter the formula

=PrevSheet(L16) to replicate across sheets.

DO NOT FORGET to ungroup sheets when done.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Enter the formula as instructed above.


Gord Dibben Excel MVP

On Tue, 22 Nov 2005 22:48:58 -0600, able72
<able72.1yxnta_1132721401.1274@excelforum-nospam.com> wrote:

>
>Here's what I want to do.
>
>I need to be able to create the initial formula, then have it retain
>relative reference. So for example, the initial formula on sheet 2
>looks like this "=sheet1!L16"
>
>On sheet 3 I want it to pull data from L16 on sheet 2. While I could
>simply write this as a single formula "=sheet2!L16" Writing this 30
>times for one formula seems redundent to say the least. How can I get
>this to change automatically?
>
>Another way to put what I want to do. . . . How do I get it to pull a
>value from the immediatly preceding worksheet within the same
>workbook?
>
>Thanks in advance for all your help.

Thanks much, for the program and the reference.