Conditional Data Validation

Conditional Data Validation will allow the drop list in a cell to change based on the input in another cell.

The first thing we need to do is create named ranges of the main and sub categories.

Select the data in each list, but not the header. In the Name box type in the header.

Note: these have to be single words.

Follow the same process for all of the sub categories, these names need to match the main category exactly.

Now lets add the first data validation. Select the first cell click the Data tab and choose Data Validation.

Choose List from allow, check Ignore Blank and In-Cell Drop down

My main category is the named range Class so my source is "=Class"

Next lets create the Conditional Validation.

Choose the next cell, choose the Data Validation drop down.

Again choose List, this time for the source were going to use the Indirect formula

"=INDIRECT(A2)" and click ok.

Because A2 is blank you will get an error. The source currently evaluates to an error. Do you want to continue? Choose Yes.

Lets test it out, I choose Forested

Now the subcategory the list is updated with the Forested list.

To copy this validation down, select and copy the two cells with the validation.

Then Select all of the cells you want to copy it to.

Right click and select Paste Special, then Paste Special again

Choose Validation from the Paste Special List,

The Conditional Data Validation is now in all of the empty cells.

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

http://www.esri.com/software/arcgis/arcgisonline/apps/esri-maps-for-office

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!

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.

Find Replace-Offset

I just finished this yesterday and Im very pleased with it.

The purpose is to Find every instance of a word in an entire workbook, and replace a cell X spaces to the right.

The code looks like this:

Sub findreplaceoffset()
Dim Stext As String
Dim Oset As Integer
Dim Rtext As String
Dim CheckVal As String
Dim ws As Worksheet
Dim result As Range

Stext = Application.InputBox("Type in the word you want to find")
Oset = Application.InputBox("Type in the Column offset to be replaced")
Rtext = Application.InputBox("Type in what you want replaced")

For Each ws In Application.ThisWorkbook.Sheets
ws.Activate
Range("A1").Activate

Do While CheckVal <> Rtext

On Error GoTo Skip:
   Set result = Cells.Find(What:=Stext, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
       
        If result Is Nothing Then
        GoTo Skip:
        End If
       
        result.Activate   
   
        CheckVal = result.Offset(0, Oset).Value
       
        result.Offset(0, Oset).Value = Rtext   
       
Loop
CheckVal = ""

Skip:
Next ws
End Sub

In this sample the workbook contains quotations with items and amounts.

You can now download the sample sheet that contains the macro and follow along.

Find_Replace_Offset_.xlsm

Today lets suppose we need to change the price of all of the Apples in every sheet to $100

Run the macro and an input box request what word you want to find.

Type "Apples"

Another input box requests the offset

Type: "1" as the pricing we want to replace is 1 space to the right of Apples

The last inpub box is the replacement value

Type:"100"

Now every ammount 1 cell to the right of apples has been changed.