Chapter 21: The INDIRECT Function


Overview

  1. My worksheet formulas often contain references to cells, ranges, or both. Rather than change these references in my formulas, I’d like to know how I can place the references in their own cells so that I can easily change my cell or range references without changing my underlying formulas.

  2. Each worksheet in a workbook lists monthly sales of a product in cell D1. Is there an easy way to write and copy a formula that lists each month’s product sales in one worksheet?

  3. Suppose I total the values in the range A5:A10 with the formula SUM(A5:A10). If I insert a blank row somewhere between rows 5 and 10, my formula updates automatically to SUM(A5:A11). How can I write a formula so that when I insert a blank row between rows 5 and 10, my formula still totals the values in the original range A5:A10?

  4. How can I use the INDIRECT function in a formula to “read” the range name portion of a formula in a worksheet?

The INDIRECT function is probably one of the most difficult Microsoft Office Excel functions to master. Knowing how to use the INDIRECT function, however, enables you to solve many seemingly unsolvable problems. Essentially, any reference to a cell within the INDIRECT portion of a formula results in the cell reference being immediately evaluated to equal the content of the cell. To illustrate the use of INDIRECT, look at the file Indirectsimpleex.xlsx, which is shown in Figure 21-1.

image from book
Figure 21-1: A simple example of the INDIRECT function

In cell C4, I’ve entered the formula =INDIRECT(A4). Excel returns a value of 6, because the reference to A4 is immediately replaced by the text string B4. Therefore, the formula is evaluated as =B4, which yields a value of 6. Similarly, entering in cell C5 the formula =INDIRECT(A5) returns the value in cell B5, which is 9.

  • My worksheet formulas often contain references to cells, ranges, or both. Rather than change these references in my formulas, I’d like to know how I can place the references in their own cells so that I can easily change my cell or range references without changing my underlying formulas.

  • In this example, the data we’ll use is contained in the file Sumindirect.xlsx, shown in Figure 21-2. The cell range B4:H16 lists monthly sales data for six products during a 12-month period.

    image from book
    Figure 21-2: You can use the INDIRECT function to change cell references in formulas without changing the formulas themselves.

  • I currently calculate total sales of each product during months 2–12. An easy way to make this calculation is to copy from C18 to D18:H18 the formula SUM(C6:C16). Suppose, however, that you want to be able to change which months are totaled. For example, you might want total sales for months 3–12. You could change the formula in cell C18 to SUM(C7:C16) and then copy this formula to D18:H18, but using this approach is problematic because you have to copy the formula in C18 to D18:H18 and, without looking at the formulas, nobody knows which rows are being added.

  • The INDIRECT function provides another approach. I’ve placed in cells D2 and E2 the starting and ending rows of our summation. Then, by using the INDIRECT function, all I need to do is change the starting and ending row references in D2 and E2, and the sums are updated to include the rows we want. Also, by looking at the values in D2 and E2, it is obvious which rows (months) are being added! All I need to do is copy from C18 to D18:H18 the formula SUM(INDIRECT(C$3&$D$2&'':''&C$3&$E$2)). If you want to see how Excel evaluates a reference to the INDIRECT function, use the following trick. Place the cursor over part of the formula (for example, C$3) and then press F9. Excel will show you the value of the selected portion of the formula. For example, C$3 will evaluate to C. Make sure to press Esc to return to Excel. Every cell reference within the INDIRECT portion of this formula is evaluated to equal the contents of the cell. C$3 is evaluated as C, $D$2 is evaluated as 6, and $E$2 is evaluated as 16. Using an ampersand (&) as the concatenation symbol Excel evaluates this formula as SUM(C6:C16), which is exactly want we want. The formula in C18 returns the value 38+91+69=607. In cell D18, our formula evaluates as SUM(D6:D16), which is the result we want. Of course, if we want to add up sales during, months 4 through 6, we simply enter 8 in D2 and 10 in E2. Then the formula in C18 returns 33+82+75=190. (For information about using the ampersand to concatenate values, see Chapter 6, “Dates and Date Functions.”)

  • Each worksheet in a workbook lists monthly sales of a product in cell D1. Is there an easy way to write and copy a formula that lists each month’s product sales in one worksheet?

  • The file Indirectmultisheet.xlsx (see Figure 21-3) contains seven worksheets. In each worksheet, cell D1 contains data about the sales of a product during a particular month. Let’s suppose Sheet1 contains month 1 sales, Sheet 2 contains month 2 sales, and so on. For example, sales in month 1 equals 1, in month 2 equals 4, and so on.

    image from book
    Figure 21-3: Monthly sales (months 1–7) of a product listed by using the INDIRECT function

  • Suppose you want to compile a list of each month’s sales into one worksheet. A tedious approach would be to list month 1 sales with the formula =Sheet1!D1, list month 2 sales with the formula =Sheet2!D1, and so on until you’ve listed month 7 sales with the formula =Sheet7!D1. If you have 100 months of data, this approach would be very time consuming! A much more elegant approach is to list month 1 sales in cell E10 of Sheet1 with the formula INDIRECT($C$10&D10&''!D1''). Excel evaluates C10 as Sheet, D10 as 1, and ''!D1'' as the text string !D1. The whole formula is evaluated as =Sheet1!D1, which, of course, yields month 1 sales, located in cell D1 of Sheet1. Copying this formula to the range E11:E16 lists the entries in cell D1 of sheets 2 through 7. Note that when the formula in cell E10 is copied to cell E11, the reference to D10 changes to D11, and cell E11 returns the value located at Sheet2!D1.

  • Suppose I total the values in the range A5:A10 with the formula SUM(A5:A10). If I insert a blank row somewhere between rows 5 and 10, my formula updates automatically to SUM(A5:A11). How can I write a formula so that when I insert a blank row between rows 5 and 10, my formula still totals the values in the original range A5:A10?

  • The worksheet named Sum(A5A10) in the file Indirectinsertrow.xlsx (shown in Figure 21-4) illustrates several ways to total the numbers in cell range A5:A10. In cell A12, I’ve entered the traditional formula SUM(A5:A10), which yields 6+7+8+9+1+2=33.

    image from book
    Figure 21-4: Several ways to sum the values in the cell range A5:A10

  • Similarly, the formula SUM($A$5:$A$10) in cell E9 yields a value of 33. As you’ll soon see, however, if we insert a row between rows 5 and 10, both formulas will attempt to total the cells in the range A5:A11.

  • With the INDIRECT function, you have at least two ways to total the values in the range A5:A10. In cell F9, I’ve entered the formula SUM(INDIRECT(''A5:A10'')). Because Excel treats INDIRECT(''A5:A10'') as the text string ''A5:A10'', if I insert a row in the worksheet, this formula still totals the entries in the cell range A5:A10!

  • Another way to use the INDIRECT function to total the entries in the range A5:A10 is the formula SUM(INDIRECT(''A''&C4&'':A''&D4)), which is the formula entered in cell C6. Excel treats the reference to C4 as a 5 and the reference to D4 as a 10, so this formula becomes SUM(A5:A10). Inserting a blank row between row 5 and row 10 has no effect on this formula because the reference to C4 will still be treated as a 5 and the reference to D4 will still be treated as a 10. In Figure 21-5, you can see the sums calculated by our four formulas after a blank row is inserted below row 7. You can find this data on the worksheet Row Inserted in the file Indirectinsertrow.xlsx.

    image from book
    Figure 21-5: Results of SUM formulas after inserting a blank row in the original range

  • Note that the classic SUM formulas that do not use the INDIRECT function have changed to add up the entries in the range A5:A11, so these formulas still yield a value of 33. The two SUM formulas that do use the INDIRECT function continue to add up the entries in the range A5:A10, so we lose the value of 2 (now in cell A11) when we calculate our sum. The SUM formulas that use the INDIRECT function yield a value of 31.

  • How can I use the INDIRECT function in a formula to “read” the range name portion of a formula in a worksheet?

  • Suppose we have named several ranges in our worksheet to correspond to quarterly product sales. (See Figure 21-6 and the file Indirectrange.xlsx.) For example, the range D4:E6 (named Quarter1) contains fictitious first-quarter sales of various Microsoft products.

    image from book
    Figure 21-6: Use the INDIRECT function to create reference to range name within a formula

  • We would like to write a formula that can easily be copied and that will then yield the sales of each product in each quarter in a single rectangular range of the worksheet, as shown in H17:J20. You would think you could enter in cell H17 the formula =VLOOKUP(H$16,$G17,2,FALSE) and then copy this formula to the range H17:J20. Unfortunately, Excel does not recognize $G17 as referring to the range name Quarter1. Rather, Excel just thinks $G17 is the text string Quarter1. The formula, therefore, returns an #NA error. To remedy this problem, simply enter in cell H17 the formula =VLOOKUP(H$16,INDIRECT($G17),2,FALSE) and then copy this formula to range H17:J20. This works perfectly! INDIRECT($G17) is evaluated as Quarter1, and is now recognized as a range name. We now have easily generated sales of all products during all four quarters!




Microsoft Press - Microsoft Office Excel 2007. Data Analysis and Business Modeling
MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)
ISBN: 0735623961
EAN: 2147483647
Year: 2007
Pages: 200

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net