There seems to be a bug in the way UDFs handle input Range parameters which
use OFFSET:
In a workbook with more than one sheet add the following UDF:

Function SheetCheck(theRange as range)
SheetCheck=theRange
end function

on Sheet 1 put 1 in cell a1
on Sheet2 put 2 in cell a1

in sheet 1 b1 put
=sheetcheck(Sheet1!$A$1:OFFSET(Sheet1!$A$1,1,0))
in sheet 1 b2 put
=sheetcheck(OFFSET(Sheet1!$A$1,0,0,1,1))

select Sheet 1 and press F9: both cells show 1
select Sheet 2 and press F9: select sheet 1 and you will see that B1 shows 2
but B2 shows 1

The formula in B1 incorrectly refers to the active sheet rather than Sheet1

(tested on Excel97 Excel2000 and Excel2002)

This bug also happens when using Defined names containing these kinds of
formulae: this makes it important when using Dynamic Range Names to use the
second type of Offset formulae rather than the first.


Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com