Tips for Excel, Word, PowerPoint and Other Applications
Introduction to INDIRECT
Why It Matters To You
Quickly consolidating similarly formatted data sets is not only a time saver, it's a life saver. Have you ever been the issuer of a request for data (maybe an RFP or you're getting quarterly sales by region)? Your 10 responses all come back Friday afternoon (your first mistake) and your boss immediately wants to know how the responses look? With a sigh, you cancel your dinner date, start up a pot of coffee, and settle in for a long night of consolidating responses.
Maybe this scenario sounds a little better. As you receive each response, you unhide a data consolidation sheet (ALT + O, H, U), Enter; more on this later), copy the sheet into your summary workbook (ALT + E, M, C), name each sheet after the bidder, and watch your data automatically get pulled into an evaluation model. You finish pulling in your last bid at 4:00, check your data, write up a summary e-mail with your observations, and then send it off before leaving early for your dinner date.
How To ...
This is one of my favorite Excel functions. When applied properly, and planned for in advance, INDIRECT is an amazing powerful tool for data consolidation and modeling. NOTE: This is an advanced level article. Continue at your own risk.
So what does it do? INDIRECT converts a text string into a variable string. Huh?
Let me explain this in another way. INDIRECT can combine the text elements of a worksheet reference and turn it into a worksheet reference. INDIRECT can take a text element like "aggressive scenario" and turn it into the named range "aggressive scenario". Explained another way, INDIRECT can take a text string that happens to look like a sheet and cell reference, and turn it into a live sheet and cell reference. Aha!!!!
Open up the sample file, excel_indirect.xls and go to the summary tab. Here, I have 2 evaluator scorecards (ok, they're my wife and my brother) with their evaluations of a car I'm looking to buy. They've each filled out a scorecard I gave them and now I want to summarize their scores and make a purchase decision. By the way, in case anyone is wondering, we did not score our last auto purchase.
Take a look at cell D5.
The easy way to consolidate values to a summary table would be to link to other worksheets. However, what if you have 100 sheets, or your values aren't incontiguous cells, or you want to set up your evaluation before bids arrive? This is where INDIRECT comes in.
First, let's take a look at the formula and unbundle it. At first glance it's pretty simple.
On further inspection, the pattern is pretty clear. It's simply the sheet name and then the cell reference separated by a "!". So, if there is some way to string all of these elements together (CONCATENATE) and turn the result into a variable string, I can actually create workbook references. Eureka!
In this example, I'm going to use & arguments instead of CONCATENATE to create a text string that looks like the workbook reference, Evaluatior!C5, and then use INDIRECT to convert it into a real reference. Just remember that any text needs to be encased in quotes. Here's what it looks like:
As a trick, I use the column headers as sheet names, which must mirror the actual sheet names, and add a column for cell references. This allows me to dynamically change cell references if I need to. One note of caution, sheet names must be contiguous. You cannot have any spaces in the name, so the worksheet must be evaluator1 and not evaluator 1. NOTE: As you might have already picked up on, in order to use this, your component worksheets need to be laid out in an identical manner, so some degree of preplanning is required.
I also use a column for cell references to enable copying the formula. If you specify the cell reference as "!C5", then when you drag the formula down a couple of rows, you'll notice that your cell reference didn't change. Again, a bit of preplanning allows you to account of the shortcomings of INDIRECT.
One of the things you have to watch out for is the #REF error if you have a bad or non existent cell or sheet reference. This can be fixed with an IF/ISERROR combo as shown below. To test this out for yourself, rename the sheet named Evaluator1 and see how the values in D5, E5, and F5 change. This combo is especially powerful if you're looking to set up workbooks in advance of receiving bids (e.g., your worksheets don't exist yet) and you don't want formulas to return errors as you start receiving bids.
Play around with this a bit. Copy the Evaluator2 sheet, rename it Evaluator3, then change the values around. Now copy the formulas in the Evaluator2 column of the Summary sheet, rename the header Evaluator3 and see what happens. Pretty cool, eh?
So, to recap, how can I use INDIRECT?
- Allows for very quick consolidation of data that is formatted and laid out in the same manner.
- Works best when you can control the layout and format of the returned data.
- Can create summary models ahead of actually receiving bids, evaluations, or responses.
Notes
| Last updated | 7/27/08 |
| Application Version | Excel 2003 |
| Author | Michael Kan |
| Pre-requisites | None |
| Related Tips |