Excel problem

[Ian]

I am an ex-Lotus 123 user. When I have a cell in a 3D spread sheet
that points to a cell in the previous worksheet in a range, ie: cell
A5 on sheet 2 may point to cell a1 on sheet 1 (Sheet1:A1 in Lotus or
Sheet1!A1 in Excel). I can't get this formula to update when I copy
this to the next sheet (ie sheet 3). In lotus copying the cell the
formula is in and pasting it to the same cell in the next sheet, would
automatically change the Sheet1:a1 to sheet2:a1, this is not happening
in Excell. when I copy the same cell in excel the formula treats the
sheet ref as absolute, therefore Sheet1!a1 stays as Sheet1!a1 and does
not update. Any ideas?

regards
Ian

 

[Gord Dibben]

Ian

If you're willing to use a User Defined Function.......

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

Say you have 5 sheets, sheet1 through sheet5.

Select sheet2 and SHIFT + Click sheet5

In B1 enter =PrevSheet(A1)

Ungroup the sheets.

Each B1 will have the contents of the previous sheet's A1

Copy/paste the UDF above into a General Module in your workbook.

If not familiar with macros and VBA, visit David McRitchie's website 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.


Gord Dibben Excel MVP