You probably did not change it in each place. Check very carefully against
Don's code.



"caldog" wrote in message
...
I still get the same error message #NAME?, and I changed the 1 to an i.
What
I also did was open a new workbook. Typed in the vba coding, put some
information down on sheet 1 and the typed in the formula "=prevsheet(a4)"
and
received the "Name" error message

"Don Guillett" wrote:

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 doesn?Tt 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.


.