INDIRECT

Oh what a gem of a formula this is.

In essence, it allows you to specify the text that you would like to be used to bring back information. So =INDIRECT("A1") will bring back the contents of cell A1. And =INDIRECT("Sheet3!A2") will bring back the contents of cell A2 from Sheet3.

But its power and sheer elegance comes when you string together chunks of references to create something more powerful. In today's example, each sheet was named after a town. A summary sheet contained those towns' names. And we wanted to bring back cell B3 from that town's sheet.

=INDIRECT("'"&A1&"'!"&"B3")

The quotes are a bit difficult to read there. But you've got a double quote, a single quote then a double quote. This basically gives you a single quote in the cell reference. (If all of the sheet names were a single word, then you wouldn't need the quotes, as Excel doesn't need quotes to refer to such sheets. But better to be safe.) After the sheet name reference, you have another single quote and an exclamation mark. So Lyme Regis would yield 'Lyme Regis'! (including the quotes).

That's exactly what you need in a sheet reference. String it together with the B3 using the abbreviated CONCATENATE function (the ampersand (&) does the same as CONCATENATE but using less keystrokes), and you get 'Lyme Regis'!B3

So it would bring back cell B3 from the Lyme Regis sheet.

I used it similarly in a truly hideous spreadsheet I received recently that contained a sheet for each day of the project, each named in DD.MM.YY format. After shooting its author, I used some INDIRECT functions to stack the data into a single consolidated view.

Summary

It allows you to refer to a cell by building up its name.

Syntax

=INDIRECT(Reference,Logical)

Reference above is the text equivalent of the cell reference.

Logical is a true/false or 1/0 value. If you leave it blank or set it to zero, it will expect a traditional A1-style reference (i.e. column letter followed by row number). If you set it to 1, then it will expect an R1C1-style reference, which can be useful if you want to count columns rather than naming them based on their letters.

• Tweet