Q: Why does a knight have a lance, armor and a solid horse?
A: All of those pieces make it a helluva lot easier to succeed inside the reality of knocking another person off of their horse.
The task of unhorsing is fairly simple in concept. But problems arise quickly when you try to do it. For one: how do you get close enough to unhorse another person?
Today in Excel, we look at a fairly simple task and weave some Excel features together to get the task closer to being battle-ready. Well be using:
- Tables
- Named Tables
- Named Ranges
- VLOOKUP
- Dropdown lists
- INDIRECT
- VBA
I won’t go too deep into the details in this blogpost.
At the end of this blogpost there is a video, and a download link
so that you can experience the details at your leisure.
TASK We want to retrieve quota info for sales reps in different cities.
SOLUTION Lay out our data, throw in some dropdown lists, VLOOKUP. DONE!
click to see larger image
NO! WE’RE NOT DONE. At this point, we have the equivalent of a guy on a horse in pants and a shirt. What’s wrong with the image below?
The quota for Calvin has resulted in an error because there’s no Calvin in London. These small things happen when a user gets distracted. The user was looking at Phoenix and Calvin, then changed the city to London, and was called away from her desk. It’d be nice to have Excel do something other than bomb out with the #N/A error. We can’t stop here.
WHAT ELSE WOULD MAKE THIS INTERFACE BATTLE-READY?
Beyond mere retrieval of the quota data, what would be helpful to us?
- If the City changes, do something other than result in #N/A
- Make it easy to add Offices
- Make it easy to add new Reps.
Thanks to Jordan Goldmeier we have a whole 7 lines of VBA code to clear a Name if we change the City:
Basically, we have a named range called Reps.OfficeDropdownRange. It encompasses cells B2 and B3.
If either cell in that range changes, the code moves to the cell that’s 0 rows and 1 column to the right, and clears that cell’s value.
That was easier than I thought. Jordan shows that Excel users ads indeed knights who have each other’s back.
Now, for the desire to add new data with minimal upheaval. We’re going to use Excel Tables to tie this whole thing together.
EXCEL TABLES: The Knight’s Armor
Introduced in Excel 2007, Table are among the most underused features of Excel. Zack Baresse, Excel MVP and author of a forthcoming book on Excel Tables, says that 1% of Excel users include Tables in their spreadsheet development. So, if you’re one of the 1%, I kindly bow to ya, ma’am (or sir).
Tables are the armor on the horse and the knight. They’re worn on the body, unlike a shield. Armor moves with the bodies. Similarly, Excel Tables are dynamic, moving and protecting our data in ways that would otherwise require complex formulas. They allow us to
- Add droplist items without digging into Excel’s Name Manager.
- Update pivot chart results if the pivot chart is tied to a Table instead of a naked range of data.
- Write formulas that refer to a set of data that changes in size.
- In Excel 2013, Tables are the foundation of Data Models that let us create a single pivot table from multiple sources of data.
Before turning you over to the video, there’s one other detail I’ll cover.
TABLE REFERENCES WITH VLOOKUP: The Knight’s Lance!
Pointing the VLOOKUP to the right range let’s us add new tables to the mix without having to tear anything apart or rewrite formulas. The trick is in the right naming conventions.
In the VLOOKUP below, the purple section refers to the lookup range and usually takes a form like: $G$2:$J$99
=VLOOKUP(C3,INDIRECT(B3&”T”),2,FALSE)
The way this formula is written, INDIRECT(B3&”T”) tells the VLOOKUP to conduct its lookup in the table named in B3 follwed by T. In the image below Phoenix is in B3 and I’ve named the table PhoenixT. In the video, you’ll see the addition of Budapest. You guessed it: after adding Budapest, we name the table BudapestT.
CHECK OUT THE GOODS!
SUMMARY
jousting photo credit: Jeff Kubina via photopin cc
knight photo credit: Pickersgill Reef via photopin cc
