I won't post this as an answer just yet, because I'm not 100% sure it's what you need, but when I need a range that shouldn't change
under any circumstances, e.g. moving, deleting cells, etc. I used a named range and
INDIRECT
. For example, if I want a range to always apply to cells A1:A50, even if I add/delete rows, I define a range as
=INDIRECT("Sheet!$A$1:$A$50")
. It's a bit of a hack, but it does work. The
$
may not be necessary, either. —
John Bensin 44 mins ago