Is there a way to define, within the same workbook, identical names that
relate to different ranges, depending on the sheet they belong to.
For instance : The name "Area" would refer to cells Sheet1!A1:A2 when
invoked from within Sheet1, or to cells Sheet2!B1:B2 when invoked from within
Sheet2.
This would let identical formulas provide different results according to the
sheet they reside in.

Such a definition is quite straightforward using VBA, something like :
ThisWorkbook.Worksheets(1).Names.Add RefersTo:="=A1:A2", Name:="Area"
ThisWorkbook.Worksheets(2).Names.Add RefersTo:="=B1:B2", Name:="Area"

But I'm actually looking for a direct Excel method, not using VBA.

Thanks for any idea

Include the sheet tab in the definition of the range:
Insert/Name/Define:
Sheet1!Area refers to
Sheet1!A1:A1

Sheet2!Area refers to
Sheet2!B2:B2
etc.

"Bernard" wrote in message
...
Is there a way to define, within the same workbook, identical names that
relate to different ranges, depending on the sheet they belong to.
For instance : The name "Area" would refer to cells Sheet1!A1:A2 when
invoked from within Sheet1, or to cells Sheet2!B1:B2 when invoked from
within
Sheet2.
This would let identical formulas provide different results according to
the
sheet they reside in.

Such a definition is quite straightforward using VBA, something like :
ThisWorkbook.Worksheets(1).Names.Add RefersTo:="=A1:A2", Name:="Area"
ThisWorkbook.Worksheets(2).Names.Add RefersTo:="=B1:B2", Name:="Area"

But I'm actually looking for a direct Excel method, not using VBA.

Thanks for any idea

Thanks, The simpler, the better ! I should have guessed that !
By the way this sould be documented in the on line help.

"Bob Umlas" wrote:

Include the sheet tab in the definition of the range:
Insert/Name/Define:
Sheet1!Area refers to
Sheet1!A1:A1

Sheet2!Area refers to
Sheet2!B2:B2
etc.

"Bernard" wrote in message
...
Is there a way to define, within the same workbook, identical names that
relate to different ranges, depending on the sheet they belong to.
For instance : The name "Area" would refer to cells Sheet1!A1:A2 when
invoked from within Sheet1, or to cells Sheet2!B1:B2 when invoked from
within
Sheet2.
This would let identical formulas provide different results according to
the
sheet they reside in.

Such a definition is quite straightforward using VBA, something like :
ThisWorkbook.Worksheets(1).Names.Add RefersTo:="=A1:A2", Name:="Area"
ThisWorkbook.Worksheets(2).Names.Add RefersTo:="=B1:B2", Name:="Area"

But I'm actually looking for a direct Excel method, not using VBA.

Thanks for any idea