Relative 'Sheet' Reference

[mortini¥]

I have a workbook, and multiple worksheets. I am referencing cells in prior
sheets by "worksheet name and cell". Is there a way to make these
'references' relative, so they may be copied?
thanks
rob

 

[Chip Pearson]

Unfortunately, there is no such thing as relative sheet
referencing in Excel.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Gord Dibben]

rob

If you're willing to use a User Defined Function.......

Function PrevSheet(rg As Range)
'Enter =PrevSheet(B2) on sheet2 and you'll get B2 from sheet1.
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

Say you have 5 sheets, sheet1 through sheet5.

Select sheet2 and SHIFT + Click sheet5

In B1 enter =PrevSheet(A1)

Ungroup the sheets.

Each B1 will have the contents of the previous sheet's A1

Copy/paste the UDF above into a General Module in your workbook.

If not familiar with macros and VBA, visit David McRitchie's website on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord Dibben Excel MVP

 

[Jack Schitt]

I sort of managed to pseudo-simulate this once, with the help of Jan Karel
Pieterse some years ago.
Define a name AllSheets that refers to
GET.WORKBOOK(1+0*NOW())
Then for each sheet define a local name ThisSheet that refers to
=RIGHT(CELL("Filename",C1),1+LEN(CELL("filename",C1))-FIND("[",CELL("filename",C1)))
to get the worksheet name.
Then use
=MATCH(ThisSheet,AllSheets,0)
to return the Worksheet index number.
Then insert the Worksheet index number into an
INDIRECT(ADDRESS(...))
sequence

Not sure if this is what the original poster was getting at.

 

[mortini¥]

Gord,

That's it exactly.
I'll check out McRitchie's websit too.
Thanks

Rob