Chronological Numbering Spreadsheets

[Chronological Numbering]

I have 30 spreadsheets in one file, and would like to chronologicaly number
them (have a certain cell display the number of that spreadsheet). I don't
want to have to go to each spreadsheet and write a formula linking that
spreadsheet to the previous one. Is there a way to have the cell in each
spreadsheet look up the value of the same cell in the previous spreadsheet
and add 1, without having to do this manually on every single one?
Thanks

 

[Dennis]

You could put this code in the workbook_Open event or as response to a button
click.
This example holds the page number in cell O1 but you can change it to your
cell

For i = 1 To ActiveWorkbook.Worksheets.Count
Me.Sheets(i).Range("O1") = i
Next i

 

[Gord Dibben]

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

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 use......................

Say you have 12 sheets, sheet1 through sheet12...........sheet names don't
matter.

In sheet1 you have a formula in A10 =SUM(A1:A9)

Select second sheet and SHIFT + Click last sheet

In active sheet A10 enter =SUM(PrevSheet(A10),A1:A9)

Ungroup the sheets.

Each A10 will have the sum of the previous sheet's A10 plus the sum of the
current sheet's A1:A9


Gord Dibben MS Excel MVP