Converting from 123 to Excel

[caldog]

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’t 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.

 

[Don Guillett]

i and 1 are not the same thing. Change to this>put code in REGULAR module>on
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

 

[caldog]

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

 

[Gord Dibben]

Did you place the Function in a REGULAR module in the workbook?

Do not type the code.

Copy and paste Don's code directly from his post.


Gord Dibben MS Excel MVP

 

[JLGWhiz]

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

 

[caldog]

Copied Don's code over the top of the one that I typed. And the code has
been placed in each of my sheets regular module and and then I type in the
formula in the cell that I want data pulled from from old sheet
'=prevsheet(N19)' and I still get the same error message #NAME?.

 

[Don Guillett]

If this is still not working
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Don Guillett]

Luke, We "have a failure to communicate"
A REGULAR module is NOT the sheet module. Put ONCE in a regular module.