I am a supporter of St. Joseph's hospice. If you find this site useful or if it helped you, consider a small donation to St. Joseph's, please.

Information on
St. 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:

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 correspondent 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 (almost) all possible argument values of GET.CELL is shown here.