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

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

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