How to Get Direct Results Indirectly

Everybody that uses Excel regularly has their favorite functions that seem to appear in most of their work, and I’m no exception. Working as I do with data extracted by Monarch, I use Excel’s various lookup functions frequently.

That said, I rarely use the Indirect function. I just never really thought that I needed to take advantage of it.

Quoting from Excel’s online help, you “Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself”. The little example that is supplied in the help certainly didn’t do much to convince me that I need to include Indirect in my list of regularly used functions.

But that opinion has changed.

The old way is too slow

I recently found myself working with an Excel file that had dozens of sheets, each of which had the same structure but reflected data for different business lines. Within each sheet there were column groups for actual results, budgeted amounts, and forecasted amounts.

I wanted to make it easy to graph the various amounts for different topics and metrics for each business line by grouping the values to facilitate developing the graphs. Since I had quite a number of graphs to develop for each of the worksheets, I decided it was best to create a sheet for each business line that summarized all of the graphing values for that business line.

I’ve done similar work previously by building the first sheet using various lookup functions to retrieve values from other sheets, then duplicating the master sheet and, using the find and replace dialog, replacing references to the worksheet names first used with other names. This can be a tedious and time consuming job that requires some care.

Develop off-sheet references quickly and correctly

Instead, this time I used the Indirect function to handle the changing sheet names for me and I couldn’t believe how quick and easy the process of developing the values for my graphs became. I was honestly shocked.

I began the work by making it easy to build all of the range references I would need by separating the components. One cell held the name of the worksheet from which I wanted to retrieve the necessary values. Then three cells would contain the range to be used within that worksheet for each group of actual results, budget values, and forecast values.

That’s really all of the information that the Indirect function needs, but there’s one important if not critical point to discuss regarding the Indirect function.

Odds are that you’ve been using Excel for awhile, and are familiar with how Excel builds off-sheet references. For instance, if you need cell A5 on Sheet1 to be ten percent of the value in A5 on Sheet2, the formula you get is:

Sheet2!A5*0.1

If Sheet2’s name is “Budget Data” however, what you get is:

‘Budget Data'!A5*0.1

This distinction is important if we want our Indirect function to work reliably. Sheet names containing spaces must be enclosed with single quote marks, whereas sheet names without spaces must not include quote marks.

With the sheet name keyed into B1, the budget values range listed in B2 as $AQ$16:$BH500, the actual values range listed in B3 as $E$16:$V$500, and the forecast values range listed in B4 as $X$17:$AO$500, I set the formula for C2 as

=IF(ISERROR(FIND(" ",$B$1)),$B$1&"!"&B2,"'"&$B$1&"'!"&B2)

C3 becomes

=IF(ISERROR(FIND(" ",$B$1)),$B$1&"!"&B3,"'"&$B$1&"'!"&B3)

and C4 becomes

=IF(ISERROR(FIND(" ",$B$1)),$B$1&"!"&B4,"'"&$B$1&"'!"&B4)

Finally, I included the Indirect function in a VLOOKUP formula similar to

=VLOOKUP(KeyValue,INDIRECT($C$2),ColumnOffset,FALSE)

Combine the INDIRECT function with Monarch v10 exports

One of the great new features in available in Monarch v10 is its new ability to export Summary in a different manner. Not a different file format, but a new level control has been introduced with which you can export data.

Experienced Monarch users will know that Monarch’s summaries offer the choice to group, and provide subtotals for, related data. Monarch v10 now allows you to export these groups individually. Well, not quite individually, per se, as you must export the entire summary, but where the exported data for each group goes is up to you.

You have two available options:

• 1) You can create individual files for each group, or
• 2) You can create different tables for each group within a single file.

What does this really mean? Let’s say that you’ve got data summarized for 26 products listed: Product A through Product Z, and you want to export to the Excel xls file format.

Option 1 will give you 26 files named “Product A.xls” through “Product Z.xls”.

Option 2 will give you 26 sheets within a single file, with a name of your choice, each sheet named “Product A” through “Product Z”.

You can export to any of the following file formats: accdb, db, dbf, htm, html, mdb, pdf, txt, wk3 (Seriously, who’s still using that? ), xls, xlsm, and xlsx.

I tested this new exporting technique with the model I discussed recently concerning using Monarch for organizing folder and file names. In about five minutes Monarch created almost 350 xls files for me.

Let’s think about this for a moment. Do you need to share data electronically, but not everything should go to everyone? Do you often customize your data packages for your audience? How long does that take you?

Remember as well that these aren’t raw data dumps from the Table window. These are fully formatted custom summary views, complete with Excel’s outlining enabled and all the other bells and whistles that Monarch summaries offer, if that’s the format you use for exporting.

Your ROI for upgrading to Monarch v10 might just be met within a couple of hours by using this one single feature alone.

Just try doing that work without Monarch. Instead, do it purely with Excel. Oh, and I’ll be generous and even give you a whole ten minutes. Start now. I double-dog dare you.

Nothing handles data like Excel paired with Monarch

Once you master Excel’s Indirect function I’m confident that you’ll envision all manner of opportunities to capitalize on Monarch v10’s new exporting abilities. Finally, here’s a bonus for you for reading this far: an Indirect function demo file (25kb).

Upgrade to Monarch v10 and now more than ever, you’ll excel with Monarch.