-
-
If you are used to other spreadsheets, you have probably noticed that references like @(0,1,0) (for the start of the second row) or @(MYDATA) (for a location) are “absolute” – they always refer to the same location no matter where in the spreadsheet they occur. And of course sometimes it is convenient, for example when making a column of consecutive numbers, to refer to nearby cells in a relative manner, either with say @(x(),y()-1,z())+1 or @(&()+&(0,-1,0))+1 or R(,-1)+1 or up()+1 to add one to the value of the cell which is one up from the current cell (all of these expressions work). Then you can fill that expression downwards and get your column of consecutive numbers.
But these sorts of relative expressions only keep working if the cells move together with the cells they refer to. If for example you have a row of cells that are all referring to the row above with a relative reference (like R(,-1)) and you insert another row in between them, your references will be all messed up. There is value to “Excel-style” references that can be used to fill and which also can move around while still just “referring to what you want.”
To provide for this need, TEAPOT has a function X(SRC, REF) to retrieve the value of the cell labeled SRC from the cell labeled REF. If the so-labeled cells move around (either the source or the reference) it will still work. This is not particularly useful in and of itself; what makes it useful is that from a cell other than REF, it gives you the value of the cell that stands in the same relation to SRC as that cell stands to REF. So in the cell to the right of REF, it will give you the value of the cell to the right of SRC; in the cell below, it gives you the cell below SRC, etc. Now you can fill a block of cells around REF with formulas contaning X(SRC,REF) and they will refer to the analogous block of cells around SRC.
Sometimes you want to make this kind of reference and fix one of the coordinates but not the others; X() has optional flags for this, as well, so that X(SRC,REF ,fix) will always be on the same layer as SRC regardless of what layer it is called from or what layer REF is on. (This flag roughly corresponds to supplying the “$” in an Excel cell reference.) Thus X(SRC,REF,fix,fix,fix) is just @(SRC), but the intent of the latter is much clearer.
You might ask as a follow-up question: Isn't X(SRC, REF) much more cumbersome than just referring to cells by coordinate and then letting Excel “do the right thing” as you copy and move either that formula or the referred-to data? The response to this is that in a typical spreadsheet, there are only a small number of fundamental references, and all other references derive from them in this way. So you generally only need a few labels, and by taking just a little extra time to apply those labels and refer to them in initial formulas, you are making the semantics of your references much clearer and in essence documenting them within your spreadsheet. This modicum of extra effort will therefore be repaid in an easier-to-use, easier-to-understand, and easier-to-maintain and update spreadsheet.