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.

10 Navigation Reminders

1. Arrow Keys - Moves one cell in each direction
2. Ctrl + Arrow Keys - Moves to the edge of current data.
3. Shift + Arrow Keys - Extends the selection by one cell
4. Ctrl + Shift + Arrow Key - Extends selection to the last nonblank cell in a column or row
5. Delete - Deletes the contents of selected Cells
6. Alt + Enter - Starts a new line in the same cell
7. Ctrl + Spacebar - Selects the entire column of current selection
8. Shift + Spacebar - Selections the entire row of the current selection 
9. Ctrl + Pageup - Goto Previous Worksheet
10. Ctrl + Pagedown - Goto Next Worksheet

Do you have a mouse with programmable buttons?

Make your Mouse Wheel Push left Ctrl + Page Up
and your Mouse Wheel push right Ctrl + Page Down

When you do this you can cycle through worksheets in Excel very quickly.
This also works with most programs that have a tabs for navigation.

Tracking your Impact

Today’s topic is about tracking the value you have added to operations through our process improvements.  I wanted to be able to quantify the cost savings I have created by streamlining certain office processes. So I have written the following code to help me do this.

Sub UseCount()
Dim Fname As String
Dim Uname As String
Dim UDate As String
Dim utime As String
Dim MacName As String

On Error GoTo ErrHandler

MacName = "Acct - Plan vs Act "
Uname = Application.UserName
UDate = DateTime.Date$

utime = Format(Now(), "m-d-yy-h-mm")

Fname = MacName & Uname & " " & utime

Open "\\Srv01\Macros\" & Fname & ".txt" For Output As 1

Close #1

GoTo error_skip:
ErrHandler:                    

error_skip:

End Sub

What this code does is places an empty text file in a folder that contains, Macro name, User Name and a date and time stamp. With each macro I recommend documenting the time savings for each process. At anytime you can quantify how much time and money you have saved your company. If the process used to take an hour a week, and now it takes only two minutes there is a significant savings to the company over the course of a year. If multiple users utilize your tools the savings is even greater.

When I write a macro for someone I add this to the end. Then modify the macro name to identify the process. You will want to path this to somewhere on your company network that everyone has access to.

The sample screen shot to the right shows the use of one of my macros over the last year. It saves an hour a week. As you build more tools you can see how quickly you can accumulate a years worth of man hour savings for the company.

This kind of data can be very helpful when your next review comes around!