INDIRECT Function (Basics)

Objective

Understanding & using INDIRECT Function.

Prerequisites

The reader/user should have basic idea of MS Excel and Functions.

Concept

When we link a cell with another cell say “=E9” it’s a direct link, we will get value from E9. Whereas with INDIRECT function we create a cell address in form of TEXT and Excel translate such TEXT into Cell address.

This is a very powerful tool/function.  Practical use will be explained in separate post/lesson.

Features of Direct Link:

·         Simple & Quick

·         Retains last value, if source file is not found

·      Changes/updates reference if row / column is inserted or E9 is cut and pasted to a new cell

Features of INDIRECT Link:

·         Once learned: Simple & Quick

·      Shows #Ref error if source file is not open (If link is within same workbook, there is not issue)

·        Do not Change/update reference if row / column is inserted or E9(Source) is cut and pasted to a new cell. It retains reference created.

Let’s Use it

            There are two ways of using it:

            Principally these both are same, just like other Excel functions where either we can type argument within equation or create a reference to another Cell.

1.1.      Reference in parenthesis:

1.1.1. =INDIRECT(“E9”) [Specific Cell];Reference appears in parenthesis

1.1.1.1.This will get you value from E9, just like “=E9”. But this is a static reference and will not change with row/column insert or in cut paste event.

1.2.      Refer to another cell:

1.2.1. This is a two step process, wherein Excel first looks for a Cell address and then from that address picks the Value.

1.2.2. =INDIRECT(D8) [Find reference in D8]; Get Value from reference      appearing in D8

1.2.3. A1 = “You are looking for me”

1.2.4. D8 = A1

1.2.5. C1 = INDIRECT(D8)

1.2.6. Result/Value in C1 = “You are looking for me”

Sample Workbook          

Prerequisites/See also (Links not activated yet)