Update frequency around here is not impressive, the Poorhouse knows. He's mainly been trapped behind a sea of Excel for a few millenia so it feels. In which case, let's liven things up, by discussing...Excel.

Whoop. Yep, here's a handy tip of something used in recent days. Not original or mindblowing but handy if you don't already know it.

No spreadsheet is complete without a few formulae to manipulate those magic numbers. A formula usually looks like "=A1 + A2" to add the contents of cells A1 and A2. But what if you don't know, at the point of writing your spreadsheet, exactly what cell you want to use? Rather, you want the cell used to vary depending on what has been input in another cell. There are a few different ways, but a quick, easy and perhaps dirty - albeit VBA free - one is utilising the wonders of INDIRECT.

Imagine, for example, this table:

The user can input, into cell B1 for example, a choice of 1 or 2. Cell C1 is our output cell. If the user types 1 in cell B1 we want it to display whatever is in A1. If the user types 2, we want it to display whatever is in cell A2

Sounds simple, but remember that formulas aren't simply text in Excel; they are direct references. Therefore you can't input a formula into C1 whose grid reference depends on another cell. Something like "=A&B1" results in #NAME? related nonsense.

Luckily, INDIRECT solves exactly that problem, converting text into a reference. You can create a formula that refers to the contents of a third-party cell, not the cell itself.

The main parameter to INDIRECT is simple a text string that evaluates to a cell reference. For instance:

refers to cell A1, and is therefore equivalent to the rather simpler:

But, because INDIRECT's parameter is a string, you can build it up out of other substrings. Here's a couple of other ways to thrillingly refer to cell A1.

But, unlike the rest of the average employer's requirements, the Poorhouse doesn't like to make things complicated just for the fun of it. But it does make the task described above quite possible. Wham this into cell C1:

B1, as you may remember, is the control cell the user types either 1 or 2 in. Imagine the user types "1" in B1. This then makes your formula into:

If the user typed in "2", then C1 now represents:

So here is the exact phenomenon we're after - a formula that depends on the value of another cell.

Don't forget that formulae can refer to other sheets & workbooks. In fact, the most recent Poorhouse use-case, he did want values to be calculated based on different worksheets depending on the value of another cell.

This is how you pluck figures from a different worksheet - here, selecting cell A1 from another worksheet that the tab name which has been typed - as text - into cell A20 of the current sheet.

Which, if you have the word "sheet2" typed into cell A20, simplifies to:

And extrapolate further to refer to different workbooks, on different disks, and so on. Coupled with the cryptic power of OFFSET, world = your oyster.

One downside - or perhaps upside in many cases - is that Excel doesn't update the INDIRECT formulae when rows are inserted, deleted and so on. Oh, and another, it doesn't seem to work directly in Excel chart data series...but of course you can build a table behind the scenes with this technique and then chart that.