Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chronological Numbering Spreadsheets
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chronological Numbering Spreadsheets
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 "Chronological Numbering" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chronological Numbering Spreadsheets
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 On Wed, 16 Jul 2008 07:53:02 -0700, Chronological Numbering m wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I calculate chronological age in excel | Excel Discussion (Misc queries) | |||
Chronological Numbering | Excel Worksheet Functions | |||
How to share a workbook when entries are chronological? | Excel Discussion (Misc queries) | |||
Automatic numbering of spreadsheets | Excel Worksheet Functions | |||
Auto numbering of spreadsheets | Excel Discussion (Misc queries) |