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.
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.
"RICHARD ANNOR" <RICHARD [email protected]> wrote...
>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).
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks