TABLE OF CONTENTS The previous sections introduced the key name concepts and techniques, sufficient for most routine modelling purposes. The following sections describe additional techniques which are less commonely used.
It goes without saying that cell C1 is the intersection between column C and row 1 and provides a unique reference. If we have row and column names we can repeat the trick with range names. If we had a column named London and a row called Sales, I could write a reference to the London Sales cell in the following way:
=London Sales
In case you have not spotted it, there is a space or 'intersection operator' between the two names. As this intersection is as unique as a cell reference, there is no requirement to write the formula in the same row or column as the named ranges. If it turns out that there is no intersection between the row and the column, Excel will return the #NULL! error. I include this short paragraph on intersection formulae out of completeness; I do not find it particularly useful, and indeed I have found it rather unstable.
It is possible to set up range names which refer to elements on different sheets, which would be useful for consolidations, but in practice little else. In this example we have three inputs sheets which have the exactly the same layout but represent the profit and loss statements for three business units.
We can create...
