offset and indirect function

[RICHARD ANNOR]

I would like to know the difference between OFFSET and INDIRECT worksheet
functions in preventing formulas from being disrupted in situations where
cell are deleted and/or inserted. Which of the 2 is the best method to use.
Thank you.

 

[Harlan Grove]

I would like to know the difference between OFFSET and INDIRECT worksheet
functions in preventing formulas from being disrupted in situations where
cell are deleted and/or inserted. Which of the 2 is the best method to use.

=OFFSET(A1,2,3,4,5) will refer to D3:H6 until any insert/delete/cut&paste
operation that moves the A1 cell, in which case OFFSET's result will change
similarly.

=INDIRECT("R"&(2+1)&"C"&(3+1)&":R"&(2+4)&"C"&(3+5),0) will always refer to
D3:H6 no matter what insert/delete/cut&paste operations you perform.

As for 'best', use both: =OFFSET(INDIRECT("A1"),2,3,4,5).