Leading the way in Microsoft Office Development

INDIRECT(ref_text, a1)

 -

INDIRECT worksheet function

Returns a text string of the contents of a given cell reference.

This function should be used when you want to change the reference to a cell within a formula without keep changing the formula itself.

This function concatenates the first argument to create a cell reference and then returns the value from that reference.

You can use the ampersand character (&) to create your cell reference referring to cells on the active worksheet.

Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.

Referencing other Cells Indirectly

It is possible to use this function to refer to different cell references on the same worksheet.

The value in cell "C2" is the cell address you want to reference, in this case "D2".

The value in the cell "D2" is 10, so this is the value that is returned from the INDIRECT function.

Changing the value in cell "C2" to the cell reference "E2" will return the number 40 from the formula in cell "C5".

Referencing another Worksheet Indirectly

Using this technique you can also refer to cell references which are on different worksheets (in the same workbook).

The cell reference is still "D2" but this time we want to refer to cell "D2" on the worksheet called "Sheet3".

Changing the value in cell "C3" to the worksheet "Sheet2" will return the value in cell "D2" from this worksheet instead.

Referencing another Workbook Indirectly

It is also possible to use refer to refer to worksheet references which are in different workbooks.

The workbook you are trying to reference MUST BE OPEN for this function to return the correct value.

If the workbook is not open then #REF! will be returned.

Referencing another Workbook (in a different folder) Indirectly

It is possible to use a this function to refer to different cell references depending on the values in the cells.

The workbook you are trying to reference MUST BE OPEN for this function to return the correct value.

If the workbook is not open then #REF! will be returned.

Things to Remember

If the "ref_text" argument is not a valid cell reference, then this function returns #REF!.

If the "ref_text" argument refers to another workbook then this workbook must be open.