giving cells on different sheets the same name

[marcs]

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.

 

[indiantrix]

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]

Indiantrix,

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

Heimir

 

[marcs]

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]

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]

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]

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.