The indirect formula

Definition

This is according to me the coolest formula in excel to use. At first when I saw this, I thought it does not make sense, but now it is one of the most time saving formulas in excel.

With the indirect formula, you can substitute the references to cells, sheets or files to a cell. This will allow you simply type in a cell where your formula must go, without having to change the formula.

The best uses for the indirect formula are obtained, when using in combination with the concatenate formula.

Example

Let's say we have a formula in a1, which is =sum(a1:a2). This is simply, but will explain the indirect formula very well.

Go input that formula quickly.... now try to link the word "a1" to a cell where you typed in "a1". It does not work, because excel things you are stupid. By puting "a1" in a indirect formula, you can tell excel that it should go to that cell link, and rather use the text in that cell as your reference.

See in belowe example. If we try and input c1 or c2, then our sum formula, will just try and use that instead.

But if we put the c1 and c2 in a indirect formula, it works perfectly.

Things you can do with the indirect formula

• Change references to sheet names automatically...contact us for more info
• Change references to files names automatically...contact us for more info

<-----Back to index