Because you are making the range yourself you are overriding XL from making
what seems like a fairly simple decision. It is just an odd quirk of UDF's
(that to be quite honest I have never run into before but when I thought
about it it made sense)...
--
HTH...

Jim Thomlinson


"studog" wrote:

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