Linking cells from the previous sheet

[Ed Davis]

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"

 

[Gord Dibben]

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

 

[Ed Davis]

I get a #VALUE! error with this code.

 

[Gord Dibben]

What is in C105 of Sheet1?


Gord

 

[Ed Davis]

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

 

[Ed Davis]

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

 

[Gord Dibben]

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

 

[Ed Davis]

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.

 

[Gord Dibben]

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

 

[Ed Davis]

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.

 

[Gord Dibben]

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