Cell names

billythekidcal

New Member
Joined
Jun 4, 2002
Messages
15
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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

New Member
Joined
Jun 4, 2002
Messages
15
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

New Member
Joined
Jun 4, 2002
Messages
15
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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?
 

Forum statistics

Threads
1,144,741
Messages
5,726,014
Members
422,653
Latest member
mntsiki

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top