I've written a formula which is named using the Name Manager. I chose it to be a Workbook Function.

The worksheet is called W1. When I make a copy of the worksheet (called W2), the formula doesn't work. When I checked the formula in W2 it is referring to worksheet W1. But the formula is a Workbook Function, so why is it still pointing to worksheet W1?

And when I check the Name Manager, the named formula has every cell reference prefixed with "W1!" which is referring to worksheet W1. Why would it do this if it's supposed to be a formula for use in any worksheet of the workbook?

So how can I make my named formula refer only to the worksheet in which it is used?

Thanks,
May