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

JustGiving - Sponsor me now!

 

Documentation

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.

Sulprobil   Get it done   Contact   Disclaimer   Download