formula to consecutively number the same cell in different wokshee
 

I have room data sheets set up in excel. Each room is a worksheet. There is a
room number in the D4 cell of each worksheet. The first room is number 100,
the next is 101, next 102, etc. consecutively numbered to the last room.

I need a formula for the D4 cell in each worksheet that will renumber all
the room numbers in all the worksheets if a room (worksheet) is added or
subtracted.

Thanks,
Geoff

formula to consecutively number the same cell in different wokshee
 

Thinking this can't be done. The tab entered doesn't automatically reference
itself based on it's location within your spreadsheet.

"Geoff" wrote:

I have room data sheets set up in excel. Each room is a worksheet. There is a
room number in the D4 cell of each worksheet. The first room is number 100,
the next is 101, next 102, etc. consecutively numbered to the last room.

I need a formula for the D4 cell in each worksheet that will renumber all
the room numbers in all the worksheets if a room (worksheet) is added or
subtracted.

Thanks,
Geoff

formula to consecutively number the same cell in different wokshee
 

Run this macro every time you add or delete a sheet.

Sub Date_Increment()
Dim mynum As Integer
Dim iCtr As Long
mynum = 100
For iCtr = 1 To Worksheets.Count
With Worksheets(iCtr).Range("D4")
.Value = mynum - 1 + iCtr
.NumberFormat = "General"
End With
Next iCtr
End Sub


Gord Dibben MS Excel MVP

On Fri, 7 Mar 2008 11:58:05 -0800, Geoff
wrote:

I have room data sheets set up in excel. Each room is a worksheet. There is a
room number in the D4 cell of each worksheet. The first room is number 100,
the next is 101, next 102, etc. consecutively numbered to the last room.

I need a formula for the D4 cell in each worksheet that will renumber all
the room numbers in all the worksheets if a room (worksheet) is added or
subtracted.

Thanks,
Geoff