Thanks for your reply.

I originally had it the way you described. But data is pasted in the
sheets every week. That means resetting the range for every cell that
calls the UDF every time that happens. I know where the data has to
start so I wrote it to find the last row of data itself. The solution
was, instead of referencing the sheet implicitly or explicitly, to use
Application.Caller.Worksheet.

If you want to see it I'll post the code
Cheers
John