Jim Thomlinson wrote:
When not specified otherwise ranges are assumed to originate in the sheet
where the formula is entered. In your example 2 when you essentially create
the range that you pass to the function you do not speicfy the sheet anywhere
so the UDF assumes the active sheet. The problem arises because you are
creating the range in in pieces. The sheet it originates from is lost.

I think I understand:

OFFSET() is volatile, so it causes a recalc on the non-active sheet.
The range that is constructed on the non-active sheet is missing a
sheet specifier, and thus *assumes the active sheet, instead of the
sheet it resides on*?

That would be the source of my confusion; I assumed, sans specifier,
the range would refer to its home worksheet.

Is there a reason for making it work like that?

....Stu