Cell names

[billythekidcal]

Does anyone know how to name cells that are specific to a worksheet? ie. so you can use the same name on different worksheets and each name refers to a range in the worksheet. When you have a name that is specific to the sheet, will show a worksheet in the defined name, but I don't know how you set up a name so that it comes up this way. Anyone?

Thanks in advance.

 

[Andrew Poulsom]

You need to preced the name with the sheet reference eg

Sheet1!MyName

 

[Aladin Akyurek]

Naming a single cell or a multicell range is done using the Name Box on the Formula Bar or via the option Insert|Name|Define. Once a name is defined, the reference of the name can be accessed from any worksheet in the same workbook.

 

[billythekidcal]

Thanks for the replies, but that doesn't work. Even with the sheet name preceeding the range I am trying to name, I can only use the name once. If I want to name cell B1 as "testname" on sheet1 and then use the same name on sheet2 to refer to cell B2 on that sheet, then can anyone tell me how to do this?
If it works, then in the define names box the sheet to which the name refers will be on the right hand side.

Anyone?

Thanks in advance.

 

[Andrew Poulsom]

Do this:

1. Select cell B1 on Sheet1.
2. Insert, Name, Define, type Sheet1!testname in the name box and click OK.
3. Select cell B2 on Sheet2.
2. Insert, Name, Define, type Sheet2!testname in the name box and click OK.

 

[Aladin Akyurek]

On 2002-10-18 12:09, billythekidcal wrote:
Thanks for the replies, but that doesn't work. Even with the sheet name preceeding the range I am trying to name, I can only use the name once. If I want to name cell B1 as "testname" on sheet1 and then use the same name on sheet2 to refer to cell B2 on that sheet, then can anyone tell me how to do this?
If it works, then in the define names box the sheet to which the name refers will be on the right hand side.

Anyone?

Thanks in advance.

You can't use the same name for ranges in two different worksheets. Names are global to the workbook.

 

[Andrew Poulsom]

On 2002-10-18 12:25, Aladin Akyurek wrote:

You can't use the same name for ranges in two different worksheets. Names are global to the workbook.

Really?

 

[billythekidcal]

andrew - i tried the steps exactly as you indicated and that doesn't work.
aladin - yes you can, i have many names that are specific to the individual worksheets, but i just don't know the best way to create them.

 

[Aladin Akyurek]

On 2002-10-18 12:28, billythekidcal wrote:
andrew - i tried the steps exactly as you indicated and that doesn't work.
aladin - yes you can, i have many names that are specific to the individual worksheets, but i just don't know the best way to create them.

I'd like to know how you manage to name Billy both cell A1 in Sheet1 and, say, cell B10 in Sheet2.

 

[Andrew Poulsom]

What do you mean it doesn't work? It works for me. Hope you haven't omitted the exclamation mark after sheet name. Or do you already have a global name testdata? If so delete it first.

What do you get in the Insert Names dialog on Sheet1?