The subtle art of indirection

…any problem in computing can be solved by adding another level of indirection…

Butler Lampson in Authentication in distributed systems: Theory and practice

I have fond memories of the first time I cracked open a book on programming in C, and encountered pointers. Pointers are variables which hold the memory address of another variable, which the programmer can then manipulate. Without going into detail about why they are or aren’t a good thing (there are some quite vigorous arguments over that), I mention them in connection with the indirection operator *, which acts on a pointer p by dereferencing it, returning the contents of the object that it points to.

Indirection in C has a (sort of) counterpart in the Excel INDIRECT() function. INDIRECT takes a string argument and evaluates it, returning the value of whatever that string refers to. So placing =INDIRECT(B1) in cell A1 creates a direct reference to cell B1. If cell B1 contains the text “C1”, then “C1” is the argument INDIRECT evaluates. A1 will contain an indirect reference to cell C1, and will return the value contained in C1. Although this might not seem that useful at first (you could after all obtain the same result by just entering “=C1” in cell A1), the use of indirect references is a powerful tool for building dynamic formulae. Chip Pearson’s site has some excellent examples of these.

One particular example I have used in the past relates to Data Validation. A common scenario with validation is to select “List”, and then to enter a named range reference in the source box. For instance, supposing you wish to restrict entries to a list of month names, contained in a range named “Months” – so, you enter “Months” in the source box. But if you wish to change the source, you need to go through the Data -> Validation dialog, and hard-code the new range reference in there. An alternative is to enter the name of the source range in a cell, and then return the source range to the validation dialog by entering “=INDIRECT(<cell address>)” in the source box. This now means that updating the cell with a new range reference will automatically update the validation applied.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s