Gord,
Damn you're good! You deserve the MVP!! It worked once I got rid of some
spelling bugs. :) I am adding this to my repetoir and will go to the site
you showed me and learn more. It's a shame at these prices some tutorials
don't come with the package. When I searched help for UDF or User Defined
Functions, I got ZIP.
I guess they want you to pay more cubic dollars to find out how to use this
stuff. BTW, I have been using spreadsheets as an amatuer since Visicalc
spread (pun intended) to the CP/M world and we were happy with our Z80s! :)

"Gord Dibben" wrote:

Ron

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

Function PrevSheet(rg As Range)
'Enter =PrevSheet(B1) on sheet2 and you'll get B1 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 53 sheets, sheet1 through sheet53.

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 InsertModule. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord Dibben Excel MVP

On Tue, 1 Feb 2005 11:13:02 -0800, RonMc5
wrote:

I use a workbook with 53 sheet to keep track of mileage info for expenses.
There are two references to the previous sheet. EG: Current sheet is sheet5!
I need to reference a cell sheet4! . I can do this now, but if I copy sheet 5
to sheet 6, the reference is still to sheet 4, instead of to sheet 5 so I
have to edit the refering cell each time to I copy to a new sheet. 51 times!
:) The last sheet is for summaries and totals and tax info.

I think I should be able to say sheet(-1)! . That looks like a good syntax
to me! :)

Any work arounds please?