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"