Excel 4 Ur Business

Overview

• My spreadsheet formulas often contain references to cells or 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.
• 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 a single worksheet?
• Suppose I’m adding up the values in the range A5:A10 with the formula SUM(A5:A10). If I insert a blank row somewhere between row 5 and row 10, my formula updates itself to read SUM(A5:A11). Can I write a formula so that when I insert a blank row between row 5 and row 10, my formula still adds the values in the range A5:A10?

The INDIRECT function is probably one of the most difficult functions to master in Excel. 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 the INDIRECT function, look at the file IndirectSimpleEx.xls, which is shown in the following figure.

In cell C4, I’ve entered the formula =INDIRECT(A4), and Excel returns a value of 6. Excel returns this value 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 spreadsheet formulas often contain references to cells or 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.xls, shown in the following figure. The cell range B4:H16 lists monthly sales data for six products during a 12-month period.

I currently compute 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 to 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 a pain 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, 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)). 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 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, say, months 4 through 6, we simply enter 8 in D2 and 10 in cell E2. Then the formula in C18 returns 33 + 82 + 75 = 190. (For information about using the ampersand to concatenate values, see post text fuctions.)

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 a single worksheet?

The file IndirectMultiSheet.xls (see the following figure) 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 instance, sales in month 1 equals 1.

Suppose you want to list each month’s sales in a single 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 a royal pain! 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 and so on.

Suppose I’m adding up the values in the range A5:A10 with the formula SUM(A5:A10). If I insert a blank row somewhere between row 5 and row 10, my formula updates itself to read SUM(A5:A11). Can I write a formula so that when I insert a blank row between row 5 and row 10, my formula still adds the values in the range A5:A10?

The worksheet named Sum(A5A10) in the file IndirectInsertRow.xls (shown in the following figure) illustrates several ways to add up the numbers in the cell range A5:A10. In cell A12, I’ve entered the traditional formula SUM(A5:A10). This formula yields 6 + 7 + 8 + 9 + 1 + 2 = 33.

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 add up the cells in the range A5:A11.

With the INDIRECT function, you have at least two ways to sum 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 spreadsheet, this formula still adds up the entries in the cell range A5:A10!

Another way to use the INDIRECT function to add up 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 the following figure, you can see the sums computed 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.xls.

Note that the classic SUM formulas that do not involve 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 involving 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 compute our sum. The SUM formulas that use the INDIRECT function yield a value of 31.

Explore posts in the same categories: MS Excel Tune Up