https://docs.microsoft.com/en-us/office/client-developer/exc...
Every instance of OFFSET in your sheet is recalculated after each cell change. I learnt that the hard way.
For example, OFFSET(B2,1,1,1,1) is a live reference to cell C3, which means you can use functions like COLUMN to investigate the range. C3 shows up nowhere in the formula, so there's no non-volatile way to implement it.
The first argument to INDEX is a "sqref" (cell, range, or set of ranges) and INDEX will error if you try to reference a cell outside of the sqref, so use of INDEX doesn't break the obvious dependency structure