Excel workbook named formula problem

[may1hem]

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

 

[SkipVought]

Hi,

Please post VBA (macro) questions in Forum707, along with your actual code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[may1hem]

There's no VBA macro involved here, just a named formula.

 

[SkipVought]

What is the formula in the Name Manager that is not working on every sheet?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[may1hem]

Ok I've found where the problem comes from. In my formula I need to refer to a relative cell 1 row above, but I also need to refer to an absolute cell on that sheet.

When I make a copy of the worksheet then the formula works ok with the relative cells in the new worksheet W2 but instead of referring to the absolute cell in the new worksheet W2 it instead refers to the absolute cell in the old worksheet W1.

Is there a way to make the formula use an absolute cell value for whichever worksheet it's used in?

 

[SkipVought]

[red]

What is the formula in the Name Manager that is not working on every sheet?

[/red]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}