Reference previous page

L

Lori Switzer

I need to make a workbook with 52 pages (1 for each week) that looks at the
previous page and updates accordingly. Right now, when I create a new page I
have been using find/replace to replace all page references to the correct
page. This is very time consuming and I am hoping to make it quicker as I
have to make 12 files this same way - one for each person.
Any suggestions will be greatly appreciated.
 
L

Lori Switzer

That looks like what I need, but I have to admit I have never worked with
macros. I have typed the SHEETOFFSET Function into a new module in the
visual basic window, but I wonder if I am saving it wrong, or if it needs
compiling or linking or something because when I try to use the function in
the spreadsheet it gives me "#VALUE! ".
 
G

Gord Dibben

Lori

You can Edit>Replace after the fact to change the sheet name which is probably
easiest.

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

That looks like what I need, but I have to admit I have never worked with
macros. I have typed the SHEETOFFSET Function into a new module in the
visual basic window, but I wonder if I am saving it wrong, or if it needs
compiling or linking or something because when I try to use the function in
the spreadsheet it gives me "#VALUE! ".
Click to expand...

Gord Dibben MS Excel MVP
 
Top