Dynamic Sheet Numbering - Even when there are spaces between them

I created this formula to help maintain sheet numbers in a sheet index.

If you have ever tried to keep track of something like this when there are gaps in the data this will save you a lot of time.

In the example shown above I only have to put the number 1 into Cell A6, then copy the forumula from A8 to all of the cells below.

Now lets look at this forumula and see how it works:

=IF(ISBLANK(B8),"",1+MAX(OFFSET(A8,-ROW()+1,0,ROW()-1,1)))

In this example what I wanted to do was to check if there was a DWG No. label.

IF there was a label I wanted to find the largest number above it and add one to it.

If B8 is blank then the formula returns nothing.

If B8 is not blank then I have used MAX together with OFFSET to define a range that equals every cell above the current cell, A8.

MAX() – Returns the Largest Number

=OFFSET(reference,rows,columns,height,width)

A8 : the reference or staring point,

-ROW()+1 Will Return -8+1 = -7  : this is the offset from the starting point in rows.

0 : The offset in columns

ROW()-1 Will return 8-1=7 : This will be the Height of the range

1 : The width of the range

No more renumbering all of the sheets in a list when you insert a new sheet into the list.