Monday, February 13, 2012

Bill Jelen's Excel 2010 In Depth book review


After interviewing him a few weeks ago, I decided to pick up one of Bill Jelen's Excel books (I must confess I had never read one before the interview, though I am very familiar with his Mr. Excel website). Microsoft Excel 2012 In Depth is a great resource. Bill expertly explains numerous new improves to Excel including the calculation engine which improves the speed and accuracy of math, financial, and statistical functions. There are several tutorials which include step-by-step instructions with icons on how to design and create templates and organize data. The first four chapters especially are incredibly useful in detailing all the changes from Excel 2007 to 2010. There is a nice mix of basics and advanced to satisfy users of all skill levels. If you're going to pick up a book to learn more about Microsoft Excel then I highly recommend this is the one to do so.

Monday, February 6, 2012

Organized Baseball Coach Spreadsheets Download

Well, now that the football season is officially over it's time to turn our attention to America's next favorite past-time: baseball! Spring is right around the corner (or will winter finally arrive) and that means spring practices for baseball will begin. There are many dads out there that coach their son's or daughter's baseball and softball teams. One of the most time consuming tasks of coaching is the ‘off-field’ administrative tasks. Well, now there is a spreadsheet template to help you baseball coaches get organized!


Organized Baseball Coach Spreadsheets  will help you to organize player-parent contact lists, pitch tracking records, 12 month season and daily plan charts, player attendance, depth charts, 40 yard time, and much more! Literally, every single aspect of coaching you can think of has a spreadsheet template built in.


Click Here to download the baseball coach spreadsheets!

Wednesday, February 1, 2012

Advanced Custom HLOOKUP Formula

About a year ago I posted an explanation how to create an advanced custom vlookup formula. Recently, I had a reader ask me how to convert this custom code into an advanced custom hlookup formula. It's not as easy as changing all the columns to rows and vice versa as the offset function needs to also be applied. Here is the custom hlookup VBA code:


Public Function HlookupNth(MyVal As Variant, MyRange As Range, Optional RowRef As Long, Optional Nth As Long = 1)
Dim Count, i As Long, cll As Range
Count = 0
If RowRef = 0 Then RowRef = MyRange.Rows.Count
For Each cll In MyRange.Rows(1).Cells
  If cll.Value = MyVal Then
    Count = Count + 1
    If Count = Nth Then
      HlookupNth = cll.Offset(RowRef - 1).Value
      Exit Function
    End If
  End If
Next cll
HlookupNth = "Not Found"
End Function
And the forumla would have this format:
=HLOOKUPNTH(lookup_value, lookup_Range, col_index_num, nth_value) 


And a reminder: don't forget to get your copy of our Super Bowl squares spreadsheet before the big game on Sunday.