Using INDIRECT in Excel

I know, it's been awhile.

Today I want to talk about a little known function in Excel called INDIRECT.  This function takes one parameter and one optional parameter.  The required parameter is a string and the optional is ture or false.  The String passed in needs to be in the format of a cell reference, you know, "A2" or "$A$2" or even "R1C1" and "RC[-1]"  The boolean (true/false parameter) is true if you are going to use the well known A1 format, and false if you are using the R1C1 format.

So why are you providing this function the string representation of a cell?  If the string is valid it will give you the value found in the cell, if not it will give you a #REF! error.  Here's an example:

	A	B
1	56.78	Bob
2	43.15	Jane
3
4	=INDIRECT("A1")

The value of cell A4 would be 56.78, since it is the value of the cell the string references to, A1.

Oh wow, I hear you saying.  Like that is a big deal.

If you have ever learned a thing called pointers in computer science, then this is the Excel equal to that, and you will suddenly realize how powerful this function can be.

The INDIRECT is usually at the tail end of a chain of other formulas that you will use to dynamically build formulas from formulas.  That's right, I heard you like Excel so I'm giving you INDIRECT so that you can formula while you formula!  (I know it's a bad meme.)  But that is exactly what INDRIECT does, it allows you to use other formulas (which I will cover later) to build a formula.  You use INDIRECT at the end to evaluate the formula that you built out of the formulas.

To give you an idea of this:

	A	B
1	56.78	Bob
2	43.15	Jane
3
4	=INDIRECT(B4)	A1

The value of cell A4 would be 56.78 again, but this time you can see that instead of the string being written at the time the formula was written, the value from B4 is being use to contain the location of the cell that we are interested in.  If I change the value of B4, I change the formula in A4.

Next post I'll show you how to use the ADDRESS function in conjuction to INDIRECT to build a formula out some parameterized cells.