Is it possible to link a cell from the "Previous Sheet" without giving it a
name.
I need to link about 400 cells on a sheet and need to copy it to another 30
sheets.
In Using the Name of the sheet I can not copy to the next sheet without
having to change the sheet name for each link.

I would like to do the following:

In Sheet1 cell C105 link to sheet 2 C5. So in sheet2 C5 I would like to use
something like "PrevSheet:C105"


--
Thank You in Advance
Ed Davis

Copy/paste this UDF to a general module in your workbook.

Function PrevSheet(rg As Range)
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

Example of usage.........in your case

Select sheets 2 through 30

On active sheet in C5

=prevsheet(C105)

Ungroup the sheets.


Gord Dibben MS Excel MVP



On Fri, 2 Oct 2009 11:02:07 -0300, "Ed Davis"
wrote:

Is it possible to link a cell from the "Previous Sheet" without giving it a
name.
I need to link about 400 cells on a sheet and need to copy it to another 30
sheets.
In Using the Name of the sheet I can not copy to the next sheet without
having to change the sheet name for each link.

I would like to do the following:

In Sheet1 cell C105 link to sheet 2 C5. So in sheet2 C5 I would like to use
something like "PrevSheet:C105"

I get a #VALUE! error with this code.


--
Thank You in Advance
Ed Davis
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Copy/paste this UDF to a general module in your workbook.

Function PrevSheet(rg As Range)
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

Example of usage.........in your case

Select sheets 2 through 30

On active sheet in C5

=prevsheet(C105)

Ungroup the sheets.


Gord Dibben MS Excel MVP



On Fri, 2 Oct 2009 11:02:07 -0300, "Ed Davis"
wrote:

Is it possible to link a cell from the "Previous Sheet" without giving it
a
name.
I need to link about 400 cells on a sheet and need to copy it to another
30
sheets.
In Using the Name of the sheet I can not copy to the next sheet without
having to change the sheet name for each link.

I would like to do the following:

In Sheet1 cell C105 link to sheet 2 C5. So in sheet2 C5 I would like to
use
something like "PrevSheet:C105"

What is in C105 of Sheet1?


Gord

On Sat, 3 Oct 2009 13:55:22 -0300, "Ed Davis"
wrote:

I get a #VALUE! error with this code.

First it was a formula of text.
I then changed it to a formula with a number.
I then changed it to a number that I typed in.
All cases it gave me the #VALUE! error


--
Thank You in Advance
Ed Davis
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
What is in C105 of Sheet1?


Gord

On Sat, 3 Oct 2009 13:55:22 -0300, "Ed Davis"
wrote:

I get a #VALUE! error with this code.

I just tried it again with plain text and nothing came over no error either.
I also tried with a number and same thing no error or data was pulled over


--
Thank You in Advance
Ed Davis
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
What is in C105 of Sheet1?


Gord

On Sat, 3 Oct 2009 13:55:22 -0300, "Ed Davis"
wrote:

I get a #VALUE! error with this code.

First............did you copy the UDF as posted to a general module in your
workbook?

If yes...........Select Sheet2 then SHIFT + Click on Sheet30...........sheet
names don't matter.

This will group those sheets.

In the active sheet enter the formula in C5 =prevsheet(C105)

All this should return on each sheet is what is currently in Sheet1 C105

If none of this works can you send the workbook to me by email along with
what you need from each previous sheet?

gorddibbATshawDOTca

Make the appropriate changes to email me.


Gord

On Sat, 3 Oct 2009 16:13:55 -0300, "Ed Davis"
wrote:

I just tried it again with plain text and nothing came over no error either.
I also tried with a number and same thing no error or data was pulled over

Hi Gord
I have found that the links do not update.
I have to hit F2 and enter for them to update.
I have auto calc on but even if I recalc they do not update.
It does not happen every time but does happen most of the time.



--
Thank You in Advance
Ed Davis
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
First............did you copy the UDF as posted to a general module in
your
workbook?

If yes...........Select Sheet2 then SHIFT + Click on
Sheet30...........sheet
names don't matter.

This will group those sheets.

In the active sheet enter the formula in C5 =prevsheet(C105)

All this should return on each sheet is what is currently in Sheet1 C105

If none of this works can you send the workbook to me by email along with
what you need from each previous sheet?

gorddibbATshawDOTca

Make the appropriate changes to email me.


Gord

On Sat, 3 Oct 2009 16:13:55 -0300, "Ed Davis"
wrote:

I just tried it again with plain text and nothing came over no error
either.
I also tried with a number and same thing no error or data was pulled over

Try adding Application.Volatile as such......

Function PrevSheet(rg As Range)
Dim N As Variant
Application.Volatile 'added this line
With Application.Caller.Parent
N = .Index
Do
If N = 1 Then
PrevSheet = CVErr(xlErrRef)
Exit Do
ElseIf TypeName(.Parent.Sheets(N - 1)) < "Chart" And _
.Parent.Sheets(N - 1).Visible = xlSheetVisible Then
PrevSheet = .Parent.Sheets(N - 1).Range(rg.Address).Value
Exit Do
End If
N = N - 1
Loop
End With
End Function

Don't know why I left that out..........weekend brain in gear when we were
working on your emailed workbook.


Gord

On Tue, 6 Oct 2009 20:25:02 -0300, "Ed Davis"
wrote:

Hi Gord
I have found that the links do not update.
I have to hit F2 and enter for them to update.
I have auto calc on but even if I recalc they do not update.
It does not happen every time but does happen most of the time.

After adding this line of code it is taking about 4 to 5 seconds to
calculate each cell I change.
With over 300 cells at a time it would take forever to calculate the whole
sheet.


--
Thank You in Advance
Ed Davis
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Try adding Application.Volatile as such......

Function PrevSheet(rg As Range)
Dim N As Variant
Application.Volatile 'added this line
With Application.Caller.Parent
N = .Index
Do
If N = 1 Then
PrevSheet = CVErr(xlErrRef)
Exit Do
ElseIf TypeName(.Parent.Sheets(N - 1)) < "Chart" And _
.Parent.Sheets(N - 1).Visible = xlSheetVisible Then
PrevSheet = .Parent.Sheets(N - 1).Range(rg.Address).Value
Exit Do
End If
N = N - 1
Loop
End With
End Function

Don't know why I left that out..........weekend brain in gear when we were
working on your emailed workbook.


Gord

On Tue, 6 Oct 2009 20:25:02 -0300, "Ed Davis"
wrote:

Hi Gord
I have found that the links do not update.
I have to hit F2 and enter for them to update.
I have auto calc on but even if I recalc they do not update.
It does not happen every time but does happen most of the time.

You have something else going on.

If you are talking about the 4 meg workbook Sales 10_2009.xls you sent me,
When I change a bunch of cells in A140:A60 in Prev Month sheet, it takes a
blink of the eye for the 31 sheets to change.

If some other workbook email to me and I'll have another look.


Gord


On Tue, 6 Oct 2009 21:21:59 -0300, "Ed Davis"
wrote:

After adding this line of code it is taking about 4 to 5 seconds to
calculate each cell I change.
With over 300 cells at a time it would take forever to calculate the whole
sheet.