giving cells on different sheets the same name

marcs

Active Member
Joined
May 3, 2005
Messages
420
I'm trying to name cells on different sheets using the same name. Excel resists this by jumping to the other sheet which has a cell with that name to make you aware of the fact. Anyone know how you get round this? There must be a way because if you make a copy of a sheet with a named cell on it, it retains that duplicate name.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

indiantrix

Well-known Member
Joined
Oct 17, 2002
Messages
943
Try naming each cell as follows:
Sheet1!thename
Sheet2!thename
Sheet3!thename
etc... that way each name will be the same, but be sheet-specific.
HTH...Larry
 

marcs

Active Member
Joined
May 3, 2005
Messages
420
Indiantrix,

thanx for that. It did the trick (or rather, trix)

Heimir
 

marcs

Active Member
Joined
May 3, 2005
Messages
420
Ah. strange! It seems to work sometimes and not others.

The sheets are named as well as numbered e.g.

sheet1(main)
sheet2(customtempl)
sheet3(details)

cell A1 in sheet 1 is named 'TOP'. (without the apostrophes)

If I then try to name cell A1 in sheet 2 as 'sheet2!TOP' it rejects it. It also rejects 'sheet2(customtempl)!TOP'. It also rejects 'customtempl!TOP'.

It seems to work fine on a new workbook but not on an existing one
 

indiantrix

Well-known Member
Joined
Oct 17, 2002
Messages
943
I understand that some punctuation marks can cause problems when used in sheet tab names. Try losing the parentheses by right-clicking on the sheet tabs and choosing "Rename", then test my method. And just for the record, don't use any spaces. Something like Sheet1Main should do quite well. HTH Larry.
 

marcs

Active Member
Joined
May 3, 2005
Messages
420
Hi Larry,

thanks for that.

I tried what you said but can't seem to be able to get rid of the parentheses. It simply reinserts them on renaming. I'll play about with other combinations.

cheers

Heimir
 

marcs

Active Member
Joined
May 3, 2005
Messages
420
It seems something odd is happening. I am able to manually rename a cell once but not a second time without closing the workbook. It simply reverts to the second name. The sheet is unprotected. Maybe a corruption somewhere in Excel. The same with using VBA to do it.
 

Forum statistics

Threads
1,181,102
Messages
5,928,069
Members
436,586
Latest member
latintxn

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