Questions answered in this chapter:
■ My worksheet formulas often contain references to cells, ranges, or both. Rather than change these references in my formulas, can I 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 one worksheet?
■ 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?
■ How can I use the INDIRECT function in a formula to “read” the range name portion of a formula in a worksheet?
■ My workbook contains sales in each country for each of my company’s products. How can I easily pool this data in a single summary worksheet?
The INDIRECT function is probably one of the most difficult Microsoft 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 immediate evaluation of the cell reference to equal the content of the cell. To illustrate the use of INDIRECT, look at the file Indirectsimpleex.xlsx, which is shown in Figure 23-1.
FIGURE 23-1 A simple example of the INDIRECT function.
In cell C4, I entered the formula =INDIRECT(A4). Excel returns the value 6 because the reference to A4 is immediately replaced by the text string “B4”, and the formula is evaluated as =B4, which yields the value 6. Similarly, entering in cell C5 the formula =INDIRECT(A5) returns the value in cell B5, which is 9.
Download from Wow! eBook <www.wowebook.com>
178 Microsoft Excel 2010: Data Analysis and Business Modeling
Answers to This Chapter’s Questions
My worksheet formulas often contain references to cells, ranges, or both . Rather than change these references in my formulas, can I 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 23-2. The cell range B4:H16 lists monthly sales data for six products during a 12-month period.
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 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.
FIGURE 23-2 You can use the INDIRECT function to change cell references in formulas without changing the formulas themselves.
The INDIRECT function provides another approach. I 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 I want. Also, by looking at the values in D2 and E2, you can see at a glance which rows (months) are being added. All I need to do is copy from C18 to D18:H18 the f ormula 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. Click in part of the formula (for example, C$3) and then press F9. Excel shows you the value of the selected portion of the formula. For example, C$3 evaluates to C.
Be 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. With the ampersand (&) included as the concat-enation symbol, Excel evaluates this formula as SUM(C6:C16), which is exactly want you want.
The formula in C18 returns the value 38+91+…69=607. In cell D18, the formula evaluates as SUM(D6:D16), which is the result you want. Of course, if you want to add up sales dur-ing months 4 through 6, you simply enter 8 in D2 and 10 in E2, and then the formula in C18 returns 33+82+75=190. (For information about using the ampersand to concatenate values, see Chapter 6, “Text 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 23-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 it equals 4, and so on.
FIGURE 23-3 Monthly sales (Months 1–7) of a product listed by using the INDIRECT function.
Now 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, Month 2 sales with the formula =Sheet2!D1, and so on, until you list Month 7 sales with the for-mula =Sheet7!D1. If you have 100 months of data, this approach is 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 $C$10 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. 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
180 Microsoft Excel 2010: Data Analysis and Business Modeling
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 23-4) illustrates several ways to total the numbers in cell range A5:A10. In cell A12, I entered the traditional formula SUM(A5:A10), which 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 you insert a row between rows 5 and 10, both formulas attempt to total the cells in the range A5:A11.
FIGURE 23-4 Several ways to sum the values in the cell range A5:A10.
With the INDIRECT function, you have at least two ways to total the values in the range A5:A10. In cell F9, I entered the formula SUM(INDIRECT(‘’A5:A10’’)). Because Excel treats INDIRECT(‘’A5:A10’’) as the text string ‘’A5:A10’’, if you 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 is still treated as a 5, and the reference to D4 is still treated as a 10. In Figure 23-5, you can see the sums calculated by the 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.
As you can see, the classic SUM formulas, which 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 the value of 2 (now in cell A11) is no longer included in the calculated sum. The SUM formulas that use the INDIRECT function yield a value of 31.
FIGURE 23-5 Results of SUM formulas after inserting a blank row in the original range.
How can I use the INDIRECT function in a formula to “read” the range name portion of a formula in a worksheet?
Suppose you have named several ranges in a worksheet to correspond to quarterly product sales. (See Figure 23-6 and the file Indirectrange.xlsx.) For example, the range D4:E6 (named Quarter1) contains fictitious first-quarter sales of various Microsoft products.
FIGURE 23-6 Use the INDIRECT function to create a reference to a range name within a formula.
It would be great to write a formula that can easily be copied and that then yields 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
182 Microsoft Excel 2010: Data Analysis and Business Modeling
works perfectly! INDIRECT($G17) is evaluated as Quarter1 and is now recognized as a range name. You have now easily generated sales of all products during all four quarters.
My workbook contains sales in each country for each of my company’s products . How can I easily pool this data in a single summary worksheet?
Cells E7:E9 of the file Indirectconsolidate.xlsx contain sales of cars, trucks, and planes on each continent. Each continent has its own worksheet. How can you summarize this data in a single sheet?
In the worksheet Summary, I created the summary shown in Figure 23-7.
FIGURE 23-7 Use the INDIRECT function to create a summary of product sales.
I copied from cell E7 to the range E7:I9 the formula INDIRECT(E$6&”!”&$C7). This formula creates the formula =LA!E7, which calculates car sales in Latin America. Copying this formula picks off the sales for each type of product on each continent.
In the worksheet Another Summary (see Figure 23-8), I created the same table in a slightly different manner. I automated the generation of the cell addresses from which I pull car, truck, and plane sales by copying from C7 to C8:C9 the formula
ADDRESS(ROW(),COLUMN()+2). This generates column E in the cell address and then rows 8 and 9. The function ROW() entered in a cell returns the cell’s row number, and the func-tion COLUMN() entered by itself in a cell returns the column number of the current cell (in this case 3). When entered as part of the third argument of the ADDRESS function, the term COLUMN()+2 effectively returns the column label for the cell two columns to the right of the current cell (in this case column E.) Then I copied from cell E7 to the range E7:I9 the formula INDIRECT(E$6&”!”&$C7).
FIGURE 23-8 Use the INDIRECT function to create a summary of product sales.
Problems
1. The ADDRESS function yields the actual cell address associated with a row and column.
For example, the formula ADDRESS(3,4) yields $D$3. What result would be obtained if you entered the formula =INDIRECT(ADDRESS(3,4))?
2. The workbook P23_2.xlsx contains data for the sales of five products in four regions (East, West, North, and South). Use the INDIRECT function to create formulas that enable you to easily calculate the total sales of any combination of consecutively numbered products, such as Products 1–3, Products 2–5, and so on.
3. The file P23_3.xlsx contains six worksheets. Sheet 1 contains Month 1 sales for Products 1–4. These sales are always listed in the range E5:H5. Use the INDIRECT function to efficiently tabulate the sales of each product by month in a separate worksheet.
4. Write a formula that will total the entries in the cell range G2:K2 even if you insert one or more columns between columns G and K.
5. The file Marketbasketdata.xlsx contains sales of various items. For each row, a 1 in columns B through K indicates a purchased item, whereas a 0 marks an item that was not purchased. In the Day Week column, a 1 means the transaction was on a Monday, a 2 means the transaction was on a Tuesday, and so on. For each item listed in K9:K14, calculate the percentage of transactions in which the item was purchased. Also calcu-late the fraction of transactions taking place on each day.
6. The file Verizonindirectdata.xlsx contains each employee’s hours of work and employee rating for January–May. Set up a consolidation sheet that enables you to choose any person and then reports that person’s hours of work during each month along with his or her overall rating for the month.
185