I hope this helps. I'm not following the formulas. I haven't used them
with that syntax.

Here is what I use to name a range that will vary in length.

=Offset('SheetName'!$A$1,1,0,CountA('SheetName'!$A :$A)-1)

It needs to be used in a column where that is filled with data right
to the end of the data fields.