Defining a custom-named Range across several worksheets

[Sven Herremans]

Hello,

I seem to have a problem:

I want to name a range across worksheets.

eg. In several worksheets in a workbook I want the cell A1 to be named
customcell1. However when I go to the naming feature (Insert > Name > Define)
it always overwrites the previous version...

I mean: when I open the window from worksheet '001' I get for range
customcell1 ='001'!$A$1.
Then when I go to worksheet 002 and open the window I get for the range
customcell 1 ='001'!$A$1. If I change it to ='002'!$A$1, then it changes the
value of the range in the 001 worksheet too, while I'm certain you can have
ranges with the same name on different worksheets. In the Insert > Name >
Define window the name than appears as customcell1
'001', however I can't do it myself it appears...

Any help/hints would be appreciated,
Best regards,
Sven

 

[Bob Phillips]

When you create the name, precede it with the sheet name (001!$A$1), and it
will be local to that sheet. Then on sheet2, use 002!$A$1, etc.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Sven Herremans]

Thank you a lot

When you create the name, precede it with the sheet name (001!$A$1), and it
will be local to that sheet. Then on sheet2, use 002!$A$1, etc.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JE McGimpsey]

Just an alternative:

Insert/Name/Define:
Name in workbook customcell1
Refers to: =!$A$1

Advantage: you can use customcell1 in every sheet in the workbook, and
it will refer to cell A1 on that sheet. No need to enter multiple
sheet-level names.

Disadvantage: you can't refer to the named range on another sheet -
e.g., =Sheet1!customcell1 will return #REF!.