Section 1.2. Addressing Cells Indirectly


1.2. Addressing Cells Indirectly

The INDEX, INDIRECT, ADDRESS, and OFFSET functions are used extensively in this book, providing formulas with much greater flexibility than cell references can provide.

All cells on a worksheet have a unique address (e.g, A1) and Excel lets you refer to any cell by its address. But what if you don't know what cell you are going to want? This can happen if the address of the required piece of information changes based on other values on the worksheet.

If there is a list of items you need to be able to select from, the INDEX function will do the job. The list can be in a column or a row. An example is shown in Figure 1-3.

Figure 1-3. Using the index function


We have a list of seven colors. In cell C4, the formula is:

 =INDEX(A1:A7,C2)

The first entry in the formula is the range where the names of the colors are. The number in cell C2 tells the formula which color is required. In this case it is color number five. The fifth color, the one in cell A5, is Blue. The formula in cell C4 returns a value of Blue as text.

The INDEX function works in most cases but sometimes you may not know the row or column of the required value. It could be anywhere on the worksheet, or even on another worksheet. Figure 1-4 contains just such an example.

This time the data extends over several columns. The value in cell D3 is 373. D3 is the fourth column and the third row. The row and column numbers are in cells D12 and D11. The ADDRESS function in cell D14 uses them to build the address.

If you need an address to include the sheet name, and you will if you are referencing data on a different sheet, use the version in cell D17. It lets you include the sheet name and builds it into the returned value.

The address alone does not help much, but the INDIRECT function returns the value corresponding to an address. In cell D20 the INDIRECT function uses the address built in cell D14 to retrieve the value in cell D3.

The formula in cell D20 could thus also be written as:

 =INDIRECT(ADDRESS(D12,D11))

Although this may seem a little complex, these functions are very useful when referencing data using both row and column values. It takes a little practice to become proficient with ADDRESS and INDIRECT, but it's worth it because they provide great flexibility in referencing data in Excel.

Figure 1-4. Working with INDIRECT and ADDRESS


Excel, as usual, offers more than one way to do things. The OFFSET function does the same thing that the INDIRECT and ADDRESS functions do, using a different approach. The example in Figure 1-5 shows how the OFFSET function handles the same situation.

Figure 1-5. Using the OFFSET function


INDIRECT uses a text string containing the address of the required cell. OFFSET uses row and column numbers from a given starting point to locate the cell. In this case the starting point is cell A1 and the offset is two rows down and three columns to the right. This is the same cell we referenced in Figure 1-4.

In most cases the choice between OFFSET and INDIRECT is a matter of personal preference. INDIRECT lets you reference a different sheet, making it better for applications that have several worksheets. The advantage of OFFSET is that it works with numbers rather than a text string. This means it does not need the ADDRESS function and can be easier to use.



Analyzing Business Data with Excel
Analyzing Business Data with Excel
ISBN: 0596100736
EAN: 2147483647
Year: 2006
Pages: 101
Authors: Gerald Knight

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