i and 1 are not the same thing. Change to thisput code in REGULAR moduleon
the destination sheet type = prevsheet(a5)

Function PrevSheet(rCell As Range)
Application.Volatile
Dim i As Integer
i = rCell.Cells(i).Parent.Index
PrevSheet = Sheets(i - 1).Range(rCell.Address)
End Function


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"caldog" wrote in message
...
The boss has told me to get our last 123 worksheet converted over to
Excel.
BTW we are using the Excel 2003 version, at work here.

Most of the formulas have copied over just fine, but there are a few that
just did not come at all. Only reason that we still use 123 is that when
we
add new worksheets, the 123 formulas automatically flow into the new
sheet,
referring back to the last old sheet, Excel only refers back to the
original
sheet that the formula was written for. Example:

Sheet A has formulas in cells A3, A5 and B5 for example
Now create new worksheets B, C, D

B will refer back to A, C will refer back to B, and D will refer back to
C.
But in Excel this doesnt happen.

As another example each page in 123 has a reference number that when I add
new pages its automatically changes by one on the new page. I can not get
this to happen in Excel.

So what I have tried but it will not work is this formula in VBA:

Function PrevSheet(rCell as Range)
Application.Volatile
Dim 1 as Integer
I=rCell.Cells(1).Parent.Index
PrevSheet=Sheets(i-1).Range (rCell.Address)
End Function.


Now I do not remember where I got this code from, so can not go back and
ask
for help, so am turning to you folks. The error message in Excel is #NAME

Have off and on been trying to this to work for awhile now.