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 |
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 |
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 |
All times are GMT +1. The time now is 07:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com