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.

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.

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€„¢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.


.

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


On Fri, 15 Jan 2010 14:06:09 -0800, caldog
wrote:

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


.

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






"Gord Dibben" wrote:

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


On Fri, 15 Jan 2010 14:06:09 -0800, caldog
wrote:

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€„¢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.


.


.

Luke, We "have a failure to communicate"
A REGULAR module is NOT the sheet module. Put ONCE in a regular module.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"caldog" wrote in message
...
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?.






"Gord Dibben" wrote:

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


On Fri, 15 Jan 2010 14:06:09 -0800, caldog
wrote:

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€„¢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.


.


.

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.


.

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
Microsoft MVP Excel
SalesAid Software

"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€„¢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.


.