I am a supporter ofSt. Joseph's hospice. If you find this site useful or if it helped you, consider a small donation toSt. Joseph's, please. Information onSt. Joseph's
 A small documentation example:You developed an Excel © solution. A substitute for the vlookup function, for example, since VLOOKUP is not relocatable and it does not allow negative column indices:
 A B C 1 Antony Miles 01/01/1970 2 Ben Smith 02/02/1980 3 Charles Miller 03/03/1960 ... 99 Value Formula 100 Smith's birth date 02/02/1980 =INDEX(\$C\$1:\$C\$3,MATCH("Smith",\$B\$1:\$B\$3,FALSE))
 VLOOKUP("Smith",\$B\$1:\$C\$3,2,FALSE) would result in Ben Smith's birth date 02/02/1980 - but if you insert a column before column C the formula won't work without change and you cannot search for a person's first name easily.So you decide to use INDEX(\$C\$1:\$C\$3,MATCH("Smith",\$B\$1:\$B\$3,FALSE)) instead. There are at least 3 alternatives to document value and formula in cells B100 and C100 in parallel: 1. [Worst] You fill B100 and C100 manually. If any of these cells changes you have to adapt the corresponding cell manually, too. This is error-prone. 2. [Better] You insert the formula in cell C100 as text with a leading ‘. Define the name EVALC with the value =EVALUATE(INDIRECT("RC[+1]",FALSE)). Set cell B100 to =EVALC. Now B100 will show the result of formula C100. Unless you insert rows before row 3, of course. 3. [Best] You insert the formula in B100. Define the name GETCELLC with the value =GET.CELL(6,INDIRECT("RC[-1]",FALSE)). Set C100 to =GETCELLC. Now C100 would show the formula used in B100. Even if you insert rows before row 3. To show even array formulas you might want to define GETCELLC as:=REPT("{",GET.CELL(49,INDIRECT("RC[-1]",)))&GET.CELL(6,INDIRECT("RC[-1]",))&REPT("}",GET.CELL(49,INDIRECT("RC[-1]",))) An overview of many possible argument values of GET.CELL is shown here.