Excel’s Indirect function allows the creation of a formula by referring to the contents of a cell, rather than the cell reference itself. Of all the functions covered in our Excel courses, it is often Indirect() that attendees haven’t come across but find an immediate use for, often saving a great deal of time and effort in the process.
If you have several sheets, each with information for a single department for example, you may want to set up a summary sheet. Rather than creating separate formulae to refer to each sheet, Indirect() can allow you to create a single set of formulae all of which use a reference to a sheet name held in a cell – hopefully an example will make this clearer.
To refer to cell A2 on a sheet named ‘Cuddly Toys’ we would use a formula like this:
However, sometimes it would be useful to be able to change a whole series of references to, for example, a different sheet.
We could type the sheet name into a cell on our main sheet, say A1. We could then write a formula to refer to cell A2 on the sheet typed into cell A1.
If we simply type:
Excel, not unreasonably, looks for a sheet named A1 and fails to find it.
However, we can use the Indirect function instead. Here is the screen from the Paste Function dialog for Indirect:
“‘“ & $A$1 & “‘!A2“
Our Ref_text entry is a little confusing, so we have highlighted the pairs of speech marks in different colours. We have two items of text, and sandwiched in between them, an absolute reference to the contents of cell A1 – as you can see this correctly returns the contents of that cell – Cuddly toys. The ampersands are used to join the 3 elements of our Ref_Text together. The first text section simply holds a single apostrophe – this is necessary because, if our sheet name contains a space, it must be surrounded by apostrophes to be correctly identified. The second section contains an absolute reference to cell A1 – the cell where we type the name of our sheet. The third text section contains the closing apostrophe for the sheet name, together with the exclamation mark that separates sheet name from cell reference, and the cell reference itself – A2.
This works well to return the contents of cell A2 on our cuddly toys sheet, and if we were to type in ‘Boardgames’ for example, it would automatically return the contents of cell A2 on a sheet named ‘Boardgames’.
However we do have a problem left to solve. We need to refer to many cells on the price list sheets, but if we copy our Indirect cell, the reference to A2 doesn’t change, because it is just text. We can solve this by using a row/column style reference instead of A2:
“‘“ & $A$1 & “‘!RC“
Note that we have to set the ‘A1’ argument of the function to ‘False’ to use this reference. RC will return the current row and column – so a formula in cell A2 will refer to A2 on cuddly toys, A3 to A3 and so on. If we need to refer to a different cell we would add numbers in square brackets after R and C. So RC would look at the cell one row down and one column right for example.