The INDIRECT Function will return the reference specified by a text string. For example, if you have a cell reference in cell A1 (e.g. A1 value = “B15”; B15 value = 25), the INDIRECT function will allow you to refer to A1 but return the value of the cell in the formula cell. In this case the INDIRECT function would return 25.

So why use the INDIRECT function if you could just refer to B15 in the first place? The INDIRECT function will add some flexibility to your worksheet, particularly if you have a very large, complicated worksheet and are constantly changing its structure (adding rows and columns, etc.)

INDIRECT

Syntax:  =INDIRECT(Cell where the reference is placed, True/False)

Arguments: 2 (1 optional)

1. String containing the reference- String can be a cell name (e.g. cell address- A1 style, Cell address R1C1 style, range name, or other text string that will define a reference.)
2. OPTIONAL: If omitted the value is TRUE
   TRUE: Cell name style of referencing
   FALSE: R1C1 style of referencing

Cell Name Referencing Style 

Using the cell name style of referencing (e.g. A1), cell B15 in this example refers to cell B10. Since cell B10 refers to cell B3, the formula returns the value of B3 which is 127.

R1C1 Referencing Style 

This example in cell B16 uses the R1C1 style of referencing to display Nancy’s February totals.

Using a Range Name 

In cell B17 the formula references the “Jan” range name. Jan is defined as the range B3:B7.

Building a Cell Reference with a Text String 

This last example builds the cell referencing using text and the contents of cells B12 and B13. Here, if your spreadsheet is constantly changing, you can just change the values in the reference cells (in this case B12 & B13) instead of altering your formula.

Using INDIRECT To Reference A Fixed Address

You can also use the INDIRECT function to build a reference to always refer to a specific cell or range even if you insert rows or columns. Suppose you always want to refer to cell A1 even if you insert rows or columns preceding the cell. If you use the $A$1 referencing in your formula, after inserting a row the reference in that cell becomes $A$2.

If you always want to refer to cell A1 no matter how your worksheet structure changes, create a range that uses the INDIRECT function.

1. Click on Insert, Name Define on the menu.
   
   
   
2. In the Name Define box, type the name of the range in the “Names in Workbook field. In this example we used “First_Cell”.
3. In the refers to field, type the INDIRECT formula using the absolute cell reference that you want to always use. Here we used $A$1
   =INDIRECT($A$1)
   
   
   
4. Click OK.
5. Now you can use the formula =(First_Cell) and it will always refer to the cell that you specified in the definition of the range even if your worksheet structure changes.

For more information on range names see Excel Range Names - Creating and Using