Excel Power Users Hit Chicago

excelbrothers02 They didn’t ask for my advice, but Mike Alexander and Dick Kusleika need a poster for their upcoming Excel and Access Power User Workshop.

And, if you can’t make it to their Excel training workshop, I’ve listed a couple of online courses that you can check out. The list is at the bottom of this post, so take a look, if you want to bump up your Excel skills!

Excel Training in Chicago

The event is in Chicago, and who better represents that city than the Blues Brothers? Your Photoshop skills are probably better than mine (non-existent), so here is my attempt at morphing Mike and Dick into The Excel Brothers. (Sorry Mike, I had to widen your face a little!)

I’ve spent many hours with Mike and Dick at Microsoft events, and they are both extremely knowledgeable, and highly entertaining. If you can make it to Chicago next month, and want to power up your Excel and Access skills, I highly recommend signing up for this workshop.

The two day workshop runs Wednesday, May 18, 2011 – Thursday, May 19, 2011, and the schedule is jam packed with sessions that will make your Excel/Access skills even more amazing than they are now.

What You’ll Learn

Bring your laptop to the workshop, so you can follow along, and hone your skills, while Mike and Dick guide you. Here’s a summary of what you’ll learn in the sessions:

  • Automate the Data Moves between Excel and Access
  • Enhance Dashboards and Reports with External Data
  • Leverage MS Query and SQL Techniques
  • Introducing new Powerful PowerPivot capabilities
  • Automate the Building of Excel Pivot Tables and Charts from Access
  • Building client-side solutions that use SQL server as the back end
  • Create Install Files for your Excel and Access Solutions.
  • Improve your VBA skills, going beyond simple Functions and Procedures

And of course, when you attend a power user workshop like this, you’ll learn even more during the breaks and informal sessions, by chatting with Mike and Dick, and your fellow attendees.

Chicago Fun

I’ve been to Chicago a few times, and it’s my favourite city in the USA. Granted, my experience is limited – I’m comparing it to Atlantic City, Orlando, Buffalo and Seattle.

If you register for the Excel and Access Power User Workshop, add a couple of days to your trip, and spend the weekend doing touristy things in Chicago.

During a visit last fall, I saw the Blues Brothers’ police car – stalled in the middle of The Magnificent Mile!

policecar

The Chicago Mayfest is celebrated May 20-22, and it sounds like fun. Who knows – you might run into Ferris Bueller!

The 17th annual Chicago Mayfest brings in three days of celebration – featuring; Chicago’s BEST Bands, Festival Cuisine, Maypole dancing, pretzels, beer, artisans, and a broad spectrum of super cool entertainment.

Just tell your boss that you want to get to Chicago for some Excel/Access training, and Maypole dancing. Who could argue with that?

bridgeview

Online Training Courses

If you can’t make it to Chicago, there are online courses to improve your Excel power skills. I highly recommend the following two training sites, based on my experiences with their Excel courses.

Power Pivot for Excel Course

This course is offered by Mynda Treacy, at My Online Training Hub

The Power Pivot for Excel Course (affiliate link) includes 5.5 hours of video tutorials covering everything from installing Power Pivot, importing data, DAX formulas, PivotTables and more. Download the sample files, and follow along with the lessons.

In this hands-on project-based course, you will build a Power Pivot model from start to finish. The training is delivered online and tutorials are available to watch 24/7 at your own pace. Pause, rewind, replay as many times as you like.

Skillwave Courses

Ken Puls, Matt Allington, and Miguel Escobar offer courses in Power BI, Power Query, Power Pivot, and Microsoft Excel.

See course details on their Skillwave website.

There’s a free course too – Power Query Fundamentals. Start with that course, to see if the content and teaching style fits with what you need.

_____________

Show Excel Scenarios With Excel VBA

Previously, we looked at using Excel Scenarios to compare high, low and medium budgets, all in the same worksheet cells.

To make Excel Scenarios easier to use, you can add a bit of Excel Scenario programming.

Make a List of Scenarios

First, create a list of scenario names, using the ScenarioList code shown below.

Next, add a data validation drop down list, so users can select one of the scenarios.

drop down list of scenario names
drop down list of scenario names

Excel Scenario Selection Code

Add the following code to the worksheet module, to change the scenario, when a selection is made in the data validation drop down list.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler
If ActiveSheet.Name = Me.Name Then
  If Target.Address = Range("Dept").Address Then
    ActiveSheet.Scenarios(Target.Value).Show
  End If
End If
Exit Sub
errHandler:
  If Err.Number = 1004 Then
    MsgBox "That Scenario is not available"
  Else
    MsgBox Err.Number & ": " & Err.Description
  End If
End Sub

Create a List of Excel Scenarios

To automatically create a list of scenarios, to use in the data validation drop down list, you can use Excel VBA.

This procedure creates a list of scenarios from the Budget worksheet, and sorts the list alphabetically.

Sub ScenarioList()
Dim sc As Scenario
Dim wsBudget As Worksheet
Dim wsLists As Worksheet
Dim iRow As Integer
iRow = 2  'leave row 1 for heading
Set wsBudget = Worksheets("Budget")
Set wsLists = Worksheets("Lists")
wsLists.Columns(1).ClearContents
wsLists.Cells(1, 1).Value = "Scenarios"
For Each sc In wsBudget.Scenarios
  wsLists.Cells(iRow, 1).Value = sc.Name
  iRow = iRow + 1
Next sc
With wsLists
  .Range(.Cells(1, 1), .Cells(iRow - 1, 1)) _
  .Sort Key1:=.Cells(1, 1), _
      Order1:=xlAscending, Header:=xlYes
End With
End Sub

More Excel Scenario Programming

Visit the Contextures website for more examples of Excel Scenario programming.

For example, if you want users to add more scenarios, turn off the error alert in the data validation cell.

Then, add a worksheet button that they can click, to add new scenarios.

macro button to add new scenarios
macro button to add new scenarios

___________________

Troubleshoot Excel With Formula View

If you’re working in Excel, there are times when things don’t go right, and you have to do a bit (or a lot!) of troubleshooting.

Lets look at a couple of quick ways to troubleshoot Excel with Formula view, to see the worksheet formulas, and a simple trick for seeing both the formulas and the results.

Continue reading “Troubleshoot Excel With Formula View”

Fix Blank Excel Cells Copied From Database

When you copy data to Excel, from another application, blank cells in the data can cause problems. Everything looks okay, at first glance, but the database blank cells don’t behave like other blank cells in the workbook. See how to fix blank Excel cells copied from a database, or created within Excel.
Continue reading “Fix Blank Excel Cells Copied From Database”

Automatically Add Sheet When Excel Opens

imageOn the Contextures YouTube channel, someone asked if Excel can automatically create a sheet when the file opens:

  • “this question from my friend, she always need to open same file and create a new sheet with the name of the month when her is updating the data. is there any ways to skip the above process?”

Video Answer

Since the question came from YouTube, I provided the answer in a video, which you can see at the end of this blog post.

If you prefer to see the code, or download a sample file, you can find the details below.

How the Add Sheet Code Works

The goal is to add a worksheet with the month name, when the Excel file opens. We only want that to happen at the start of the month, if the sheet doesn’t exist already.

We’ll write a macro that:

  • figures out what the current month is
  • checks for a sheet with that name
  • adds that sheet, if it doesn’t exist

The Add Worksheet Code

Insert a regular module in the workbook, and paste in the following code. I used yyyy-mm as the sheet name format, but you could use a different format.

For example, to see the full month name, use mmmm as the format.

Sub AddMonthWkst()
Dim ws As Worksheet
Dim strName As String
Dim bCheck As Boolean
On Error Resume Next
strName = Format(Date, "yyyy_mm")
bCheck = Len(Sheets(strName).Name) > 0
If bCheck = False Then
  Set ws = Worksheets.Add(Before:=Sheets(1))
  ws.Name = strName
End If
End Sub

Run the Code Automatically

To make the code run automatically, when the workbook opens, you’ll create a Workbook_Open event.

Paste the following code on the ThisWorksheet module:

Private Sub Workbook_Open()
    AddMonthWkst
End Sub

Download the Sample File

To see the workbook and the Add Worksheet code, you can download the Add Worksheet sample file.

The file is in Excel 2007 format, and zipped. It contains macros, so you’ll need to enable them, to see the code working.

Watch the Add Worksheet Automatically Video

To see the steps for creating the code, and making it run automatically, you can watch this Excel Video Tutorial.

_____________

Excel Function Friday: Track Driver Hours

Thanks for your formula suggestions on Wednesday’s blog post about promotional pricing.

Here’s another formula example, and I’m sure you’ll have alternate methods for this problem too.

Driver Limits

In some countries, there are limits to the hours that truck drivers can work in a string of consecutive days.

In this example, the limit is 60 hours, in any period of 7 consecutive days.

Worksheet Entry Cells

The maximum hours is entered in cell C1, and the number of consecutive days is entered in cell F1.

If the regulations change, it will be easy to change those settings.

Worksheet Entry Cells
Worksheet Entry Cells

Calculate Remaining Hours

To help prevent drivers from going over their limits, we’ll set up a table where the daily hours are entered.

The date and driver name are entered in each row, in columns B and C.

In column D, the following  SUMPRODUCT formula calculates how many hours the driver has remaining, in the current 7 day period.

=$C$1-(SUMPRODUCT(--($B$4:$B4>=$B4-F$1-1), --($C$4:$C4=$C4), --($E$4:$E4))-E4)

The SUMPRODUCT formula checks all the rows above the formula’s row, where:

  • the date within the 7 day range
  • the driver name matches the name in the current row.

That amount is subtracted from the maximum hours allowed.

DriverHours01

Calculate the Consecutive Hours

The current hours are typed in column E, and a simple formula in column F calculates the total for a consecutive 7 day period.

=$C$1-D4+E4

DriverHours02

Highlight the Violations

With Conditional Formatting, you can highlight any cells where the total consecutive hours exceeds the maximum allowed.

  • On the Ribbon’s Home tab, click Conditional Formatting
  • Click Highlight Cells Rules, and then click Greater Than

DriverHours03

  • In the Greater Than dialog box, select cell C1 as the limit in the text box.
  • Select one of the preset format, or create a custom format to highlight the cells.

DriverHours04

View the Results

With the conditional formatting applied, it’s easy to see where the trouble is.

In this example, Lou has gone over the limit on April 10th.

DriverHours05

Download the Driver Limit Sample File

To see the data and the formulas, you can download the Driver Hours Limit sample file. The file is zipped, and is in Excel 2003 format.

There is also a pivot table that totals the drivers’ hours per calendar week.
__________

Excel Price Lookup: VLOOKUP or INDEX

This week, Glen emailed me for advice on extracting prices from a lookup table. Some products have a promotional price each month, but other products are sold at the regular price.

Pricing Lookup Table

I’m blocking email attachments these days, so I can’t show you the exact setup of Glen’s Excel worksheet.

However, a simplified version might look something like this:

Pricing Lookup Table
Pricing Lookup Table

Use VLOOKUP to Find Pricing

In his email, Glen mentioned that he is using a VLOOKUP formula.

  • If there is a promotional price, he wants VLOOKUP to return the value from the Promo Price column.
  • If there is no promotional price, Glen wants the price from the Regular Price column.

Use IF Function

To do that, Glen could use the IF function, with VLOOKUP:

=IF(VLOOKUP(F3,$B$3:$D$6,2,0)=0,
VLOOKUP(F3,$B$3:$D$6,3,0),
VLOOKUP(F3,$B$3:$D$6,2,0))

IF function with VLOOKUP
IF function with VLOOKUP

CHOOSE the Right Price

Another option is to use the MATCH function to find the row that the product is in.

In the screen shot below, the following formula is in cell H3:

=MATCH(F3,$B$3:$B$6,0)

use the MATCH function to find the row
use the MATCH function to find the row

CHOOSE and INDEX Functions

Next, in cell G3, use the CHOOSE function and the INDEX function, to get the correct price:

=INDEX(CHOOSE((INDEX($C$3:$C$6,H3)>0)+1,
$D$3:$D$6,$C$3:$C$6),H3)

PromoPrice04

How the CHOOSE Formula Works

In this example, the CHOOSE function selects the correct pricing column to use for the prices. The outer INDEX function returns the price from the selected column.

First, the inner INDEX function returns the price from the promo column, for the selected product, and we check to see if the price is greater than zero:

INDEX($C$3:$C$6,H3)>0

  • If there is NO promo price, the result is FALSE (0)
  • If there IS a promo price, the result is TRUE (1)

Next, we add 1 to that result, so

  • FALSE=1
  • TRUE=2.

CHOOSE the Range

Next, the CHOOSE function returns a reference to the selected range.

  • FALSE (1) = $D$3:$D$6
  • TRUE (2) = $C$3:$C$6

Finally, the first INDEX function returns a price from the selected column, in the row for the selected product.

How Would You Solve the Problem?

I’m sure there are several other ways to solve Glen’s lookup problem. What formula would you use?
________________