In the previous post, The Secret is in the Names, I wrote about how Monarch populates Excel files with useful named ranges when exporting data. Today we’ll look at how we can capitalize on those named ranges with specific Excel functions.
In that previous post, the first point regarding the range names is that Monarch creates all of its names with an underscore if it needs to replace any space characters in our summary names. We’ll keep that in mind as we proceed.
One of the things that I like to utilize when building Monarch summaries is setting a key field to display its values across the top of the summary above the measure values, instead of down the left side of the measure values. This is particularly useful when working with dates or quarters, such as “12/1/2012”, “Dec-2012”, or Q4-FY12.
When working with a number of summaries, even across multiple models, I like to stick to the same title formatting. Plan ahead, and decide upon the time period (calendar months, fiscal periods, quarterly, what have you) that you’ll be using for reporting values. The reason for this will become even more apparent later when we look at using specific values in that selection of titles in individual Excel formulas.
For demonstration purposes, I’ll refer to the sample report files that are included when Monarch is installed, specifically the Classical Music Distributor reports, ClassJan.prn, ClassFeb.prn, ClassMar.prn and Classic.prn (which has April data). Datawatch has graciously given me permission to use those files for demonstration purposes here at ExcelWithMonarch.com
Each report has date on which the customer’s order was shipped, but we’ll choose to report shipments by calendar month. For simplicity we’ll present the date as m/d/yyyy, using the first of the month in each case. To get the Period field, we’ll build a calculated Date type field with this expression:
This will give us 1/1/2008, 2/1/2008, and so on. As mentioned, these become the column headings in the summaries that will be prepared.
Later, when we want to determine which column in the worksheet contains the February values, which we built another named range for called Reporting_Period, we can use a formula similar to:
and if the February date is in column D on the worksheet that Monarch exported, then the formula will return a value of four, meaning that the February date was found in the fourth position. This is immensely useful. The zero value at the end tells to Excel to find the exact search value.
Look at that MATCH() function again. It’s simple and easy to read/review, but there’s some else very convenient about it as well. What do we need to change if we want to use that formula to refer to a different worksheet? Correct: only the worksheet name.
As easy as that is to build, we can make it just a little bit easier to build a whole collection of similar formulas that refer to many worksheets. Since we’ll probably have quite a number of summaries in our models, we’ll also have a number of exported worksheets to help in our analysis of the data. While we could just revise the sheet name in each formula, there’s an even easier way: have Excel build a portion of the formula for us! OK, now you’re thinking, “What is he talking about?” We’ll use Excel’s INDIRECT() function. It works like this:
- Type the exact name of the exported worksheet in a cell, say A8.
- Now in cell D8, the formula becomes:
-
=MATCH(Reporting_Period,INDIRECT($C10&”!Print_Titles”),0)
-
Great, now we know how to find the proper column for a certain period, when the values go from left to right across the worksheet. But MATCH() also works nicely for vertical lists. That means that we can find where the first occurrence of “Big Shanty Music” is in a previously sorted list of customers, where the customer name was the first down key field in a summary. In cell B10, that would look something like this:
Since we know that the customer name is the first column, we can tell Excel to look in the entire column for our search name. Yes, for the Excel-knowledgeable this is a little inefficient, and there are other ways, but this works very well when the data in your Excel file isn’t too large (and these Excel files typically aren’t too large when exporting Monarch summaries – after all, that’s why we summarize with Monarch: to shelter us from vast amounts of data).
Great, now we know how to get a column number and a row number. “But Sandy, why do I care?” you ask.
Getting the row and column numbers makes the whole thing come together and you can practically hear your data make the beautiful sounds of a full symphony. All that we need now is one more Excel function, and it takes full advantage of one last Monarch exploit. Remember the third point in the Monarch secrets? Monarch creates a named range for the entire summary data table for each exported worksheet.
When we put together a cell that gives us a row number for “Big Shanty Music” (in B10), and another that calculates the column number for February 2008 (in D8), then we can use Excel’s INDEX() function in cell D10 to tell us what the value is for Big Shanty Music in February 2008, like this:
Short, clear, and lightning-quick. Formulas with the INDEX() function calculate much more quickly than the often used VLOOKUP() function. Even better, as Monarch users we can take advantage of Monarch practically handing all of the building blocks to us on a silver platter.
You can learn much more about what to do with these Excel functions that can capitalize on your Monarch data by reading the excellent eBook “Dashboard Reporting with Excel” by Microsoft Excel MVP Charley Kyd.
I’ve prepared a little demonstration of an Excel dashboard report using some of Charley’s techniques, and some of my own favorites too of course, with the Classical Music Distributors sample report data. We’ll have a look at that next time. For now though, if you can spend a few minutes getting accustomed to these functions then you’re bound to dream up your own ways to excel with Monarch.
P.S.: Don’t forget that the “30 Days to Become a Better Monarch Modeler” training package, which details my initial experience with Charley’s book, is only available at the reduced price until March 31st. Get your copy today!
