Referencing Relative Sheets

[jwatters]

How do I reference the worksheet immediately preceeding the current
worksheet? I would then like to copy that formula onto the next worksheet,
so that it automatically adjusts to reference the current worksheet (which is
its preceeding worksheet).

 

[jwatters]

How do I reference the worksheet immediately preceeding the current
worksheet? I would then like to copy that formula onto the next worksheet,
so that it automatically adjusts to reference the current worksheet (which is
its preceeding worksheet).

 

[JBeaucaire]

I've done this very thing using INDIRECT function, but to be able t
smoothly work, your worksheets need to be named in a numerical way.
Simplest is to just name the sheets 1, 2, 3, etc..

Then use INDIRECT to build up a cell reference missing the sheetnam
itself, then use any standard progressive method to increment from shee
to sheet based on the existing sheetname

I did this very thing on the attached spreadsheet to keep a runnin
total going from sheet to sheet without needing to adjust the "totals
formula each time

The formula in cell C12 adds C11 on this sheet to C12 from the previou
sheet
=C11+INDIRECT((MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,256)-1)&\"!\"&\"C12\"

+-------------------------------------------------------------------
|Filename: Running Total.xls
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=57
+-------------------------------------------------------------------

 

[T. Valko]

=C11+INDIRECT((MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,256)-1)&\"!\"&\"C12\")

Here's the formula with all the web formatting junk removed:

=C11+INDIRECT((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-1)&"!"&"C12")

 

[jwatters]

I found this response in another similar inquiry --- this resolved my problem:

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


Function PrevSheet(rg As Range)
'Enter =PrevSheet(B1) on sheet2 and you'll get B1 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 12 sheets, sheet1 through sheet12.

Select sheet2 and SHIFT + Click sheet12

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

========================================