Home	Excel	Access
| Feedback | Drop down boxes | Offset | Worksheet names |

Excel Indirect function

The INDIRECT function allows you to use a text string containing a range or cell reference which can be fed into a formula. For example the formula =INDIRECT("C3") would return the value in cell C3. This is the same as saying =C3 and is therefore normally pointless. The example (right) achieves the same result but looks up the text cell reference from within another cell (C6).

You may wish to make a workbook interactive in order to display various results which are obtained by using values from alternative ranges. (Sometimes the OFFSET function is the best way of achieving this.) The INDIRECT function becomes very useful when you wish to select various ranges of data from a collection of alternative worksheets.

In the following example there are separate worksheets for each month of the year and each one contains a list of data relating to hospital operations.

On a summary page users can select a month from a drop down box and the totalled data for the appropriate period will then be displayed. The drop down box ensures the accuracy of the selections so that only valid worksheet names can be chosen.

Cells H19 and H20 both use the INDIRECT function in order to SUM() the data from the appropriate worksheet. In order to achieve this, it is necessary to construct range references incorporating the month name shown in the drop down box. To sum the number of patients in column C on the worksheet named April, [cell H19] =SUM(INDIRECT(L20)). The formula gets the appropriate range as a text value from cell L20.

The drop down box is linked to cell L17 and returns a numeric value (1). Cell L18 uses OFFSET() to look up the name (April) from the list which was the original source of the drop down box. Cells L19 and L20 can then compose an appropriate string of text which will act as a source for the INDIRECT functions. [L19] = "'" & L18 & "'!B4:B1000" Note that the additional quotation marks which have been placed around the worksheet name are necessary for names containing spaces and more than one word.

Would you like to learn more?

Excel 2003 Formulas by John Walkenbach (with CD)

If you have named ranges of cells, these can be supplied as alternative arguments for an INDIRECT function. If cell A1 contains the word 'RangeOne' and there is a named range called RangeOne containing a series of values, the function =SUM(INDIRECT(A1)) will total it.

This is not a comprehensive explanation of the different results that can be achieved with the INDIRECT function and the Excel Help will offer some further advice. Another related function is ADDRESS() which converts numbers into a range reference text string which could in turn feed into INDIRECT().

Get this information as a document accompanied by Excel worksheets

Click here for details about obtaining this file