Controlling Defined Name Scope

[Cyberdude]

When I define a name for cell A1, then Excel creates the ?Refers to? formula by prefixing the active sheet?s name to the A1 so it looks like: =Sheet1!$A$1.

My question is, can I define a name so that it will refer to the cell A1 on whatever the active sheet happens to be at the moment? In other words, can the definition be made so that the ?Refers to? listing omits the sheet name prefix?

A variation of this question is: can I define a name so that it would apply to only certain sheets, and not to others?

This would be handy if I want to write a date/time stamp in the same location of each sheet that I update.

[Norie]

Cyberdude

I just created a named range that referred to =!A1.

I then put =NamedRange in cells on all the sheets in the workbook and enter values in A1.

The cells with =NamedRange did return the value in A1 of the sheet they were on but they didn't seem to update automatically.

ie I had to goto the cells, F2 and enter.

Don't know if that's something to do with my current setup or with what I tried.

[mdmackillop]

Try calling your cell Print_Area! That seems to work

[Cyberdude]

Surely you jest . . . "Print_Area!"???

OK, I defined cell A1 to have the name "TestCell", then I wrote (giggle) =Print_Area!A1 in the Refers to area.
At first it seemed to be OK. Then when I referred to it in a formula, I got an "Update Values: Print Area" window, which I cancelled. Then I looked at the "TestCell" definition again, and it had been changed to =Print_Area!IV65534

I suspect that is not a workablesolution, but thanks.

[jmenche]

Can't you write a Worksheet_Activate sub that will redefine your range?

[Andy Pope]

Try adding a name with the Refers to

=OFFSET(!$A$1,,,,)

Created by Bob Umlas and David Hager

[Norie]

Andy

Nice one, and it updates automatically.