Thursday, August 22, 2013

Mapping your Data - GIS In Excel!!!!

For all of you out there that have GIS departments and have subscriptions to ESRI I want to share an exciting tool that comes free with your subscription.

Esri Maps for Office

This exciting plug-in allows you to create interactive maps of your data right in excel.
Lets take a quick look

Once installed you will find a new ribbon tab in Excel "Esri Map
Clicking Insert Map insterts a dynamic map of the world
Now lets Add Excel Data.
There are several options for Location Type Choose the one that fits your data needs.

I am using Sample data that contains world wide shipping orders. So I am going to use World City
The next pop-up will ask you to define the city and country in your table


Ill choose ShipCity for the City and ShipCounrty for the Country
Now pins are put on the map for every entry in the table.

There are options to Group the Data
You can zoom in on the map and it will regroup as you move around
Clicking on one of the items in the map pulls up a window you can thumb through each entry
You can utilize and of ESRI's base maps from Aerials, Oceans, or Street Maps.
There are several other options including layer styles, heat spots, you can even enrich a layer with public data such as census data.
For more information check out ESRI for Office

Tuesday, February 26, 2013

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.

Tuesday, February 12, 2013

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.

Tuesday, February 5, 2013

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!

Thursday, January 17, 2013

SUMIFOR

Sum, If, Or: My modification of the SumIf where I needed to add a range of items.
 
Function SumIfOr(CompareRange As range, CriteriaRange As range, SumRange As range)
  Dim Subtotal As Double
  Dim Acell As range
 
  Subtotal = 0
   
  For Each Acell In CriteriaRange
 
 Subtotal = Application.SumIf(CompareRange, Acell.Value, SumRange) + Subtotal
   
  Next Acell
     
    SumIfOr = Subtotal
   
End Function
 
This could be easily modified to work as a CountIfOr as well.