INDIRECT Function

The INDIRECT 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.
You must include single quotes around any worksheet names that contain spaces.

Referencing another Workbook Indirectly

It is possible to use this function 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.

You must include single quotes around any worksheet names that contain spaces.

Referencing another Workbook (in a different folder) Indirectly

It is possible to use this function to refer to workbooks that are in different folders.
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.

Important

If your cell references include a worksheet, workbook or folder location then they need to be enclosed in single quotes.
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.