MeadInKent
| Feedback | Drop down boxes | Offset | Worksheet names |

Excel Indirect function

simple example of the excel indirect worksheet function

The Indirect function is normally used in conjuction with other functions and can supply a range reference as a variable to a formula. It lets you change the reference to a cell within a formula without changing the formula itself.

If cell (e.g. C6) contains some text (e.g. 'C3') which represents a range, a formula such as =INDIRECT(C6) will take the contents of C6 as an argument and and use it to return the contents of cell C3. If C6 contains the text 'C3:C4' the formula =SUM(INDIRECT(C6)) will add up all of the values in the range C3:C4.

This is only of benefit if there are other calculations taking place which generate alternative text/range values in 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.

1. Various data worksheets named as months

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.

2. Summary of data for period in dropdown box
-
3. Calculations dependent upon dropdown box

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.

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.

Another related function is ADDRESS() which converts numbers into a range reference text string which could in turn feed into INDIRECT().

excel Get this information as a document
accompanied by Excel worksheets
Document is in PDF format Click here for details about obtaining this
file. It has been rewritten for Excel 2010.


file: xl-indirect.htm © meadinkent.co.uk 2016 CMIDX S6 P3 Y Last updated Feb14