Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Can't change cell name

5 views
Skip to first unread message

John Hite

unread,
Jan 16, 2003, 10:09:58 PM1/16/03
to
I have several worksheets. I am trying to change the name of a cell in
the third worksheet but when I hit enter it jumps to the same cell in a
worksheet far to the right and the cell name in the 3rd worksheet
remains unchanged. The 3rd worksheet is a copy of the one to which it
jumps.

Thanks,

John Hite

JulieD

unread,
Jan 16, 2003, 10:45:46 PM1/16/03
to
Hi John

AFAIK you can't change the name of a cell - you need to go into Insert /
Name /Define and delete the old name (or change the "refers to bit") then
you can create a new name for the cell.

Regards
Julie

"John Hite" <john...@texoma.net> wrote in message
news:3E277406...@texoma.net...

Myrna Larson

unread,
Jan 16, 2003, 11:03:57 PM1/16/03
to
You can't have one name that refers to more than one cell. If the name is in use, you can't make
the changes from the Name box at the left end of the formula bar. You have to use
Insert/Name/Define dialog box.

John Hite

unread,
Jan 17, 2003, 12:34:34 AM1/17/03
to
Thanks to both of you for posting. Seems part of the problem was that
Excel doesn't like cell names that start with 'o' such as 'other'. The
far right sheet had lost all of its cell names so I deleted it. Also the
formula had lost a reference. It is working now, thanks.

Norman Harker

unread,
Jan 17, 2003, 1:49:25 AM1/17/03
to
Hi John!

Let's get what you can and can't do with names in the name box clear.

1. If no name exists for a cell or range you may type the name in the name
box rather than use insert name define.

2. If the cell or range already has a name you may type a new name in the
name box and it will be accepted as a name for that cell or range as long as
it is not a name that is currently used elsewhere. However, the first name
given will still exist and will still be the one shown in the name box when
you select the cell or range. In formulas etc. you will still be able to use
both the new name and the old name. This will remain the case until you use
Insert > Name > Define, select the old name and delete it.

3. It's not good technique to have more than one name for the same cell or
range. If you stuff up with spelling a name or decide you want to be more
explicit or decide to change the cell a name refers to you are better off
using Insert > Name Define as the method of executing changes.

--
Regards

Norman Harker
Sydney, Australia
Excel & Word format Function Lists free to good homes by direct request to:
njha...@optusnet.com.au

"John Hite" <john...@texoma.net> wrote in message
news:3E277406...@texoma.net...

JulieD

unread,
Jan 17, 2003, 9:19:20 AM1/17/03
to
Hi John

I'm a bit concerned your Excel doesn't like the word "other" - mine's quite
happy with it - AFAIK the rules on names are:
1) no spaces
2) underscore is the only allowable character
3) must start with an alpha character
4) can't look like cell references
5) max length 255
6) can't be names of functions (excel will cope if they are the names of
built-in functions but it can't handle the names being those of functions in
the analysis pak or UDF's - thanks to Norman Harker for this rule :) )

Hope this helps

Regards
julie

"John Hite" <john...@texoma.net> wrote in message

news:3E2795EA...@texoma.net...

John Hite

unread,
Jan 17, 2003, 9:30:59 AM1/17/03
to Norman Harker
Hello again, Norman. And thanks again, Norman. That bit about the old
name is ringing true. That's prezactly what I missdid, tried to change
the name of the cell in the name box w/o deleting.

Have a Foster's on me.

jh

Ken Wright

unread,
Jan 17, 2003, 10:22:18 AM1/17/03
to
> You can't have one name that refers to more than one cell.

Just to clarify for the OP though, you can have more than one non contiguous
cell in a single name, eg the following could be assigned to a name:-

=Sheet1!$G$8+Sheet1!$I$8+Sheet1!$K$8

--
Regards
Ken....................... Win XP / XL2K & XLXP

----------------------------------------------------------------------------
Public Service Request - It is very much appreciated
in text-only groups if you don't attach files - Thanks
----------------------------------------------------------------------------

"Myrna Larson" <myrna...@charter.net> wrote in message
news:btve2v8almikae09g...@4ax.com...

Norman Harker

unread,
Jan 17, 2003, 2:18:52 PM1/17/03
to
Hi John!

And here's a best kept secret of Excel but it comes with a health warning.

Tools > Options > Transition
Check Transition Navigation Keys

Now:
/RNR

Removes every single name in the workbook!

Beats doing them one at a time although you can use VBA or various Add-Ins.

But it really ought to come with a health warning after the /RNR command as
it can't be undone! And with many hundreds of names in a workbook, R is next
to T and if you really wanted a Table of your names... Don't work on Excel
when your Mother is in the room!!

--
Regards

Norman Harker
Sydney, Australia
Excel & Word format Function Lists free to good homes by direct request to:
njha...@optusnet.com.au

"John Hite" <john...@texoma.net> wrote in message

news:3E2813A3...@texoma.net...

Norman Harker

unread,
Jan 17, 2003, 3:08:47 PM1/17/03
to

Correction!

All that's needed for the Lotus / facility is

Tools > Options > Transition
"dot" Lotus 1-2-3 Help

--
Regards

Norman Harker
Sydney, Australia
Excel & Word format Function Lists free to good homes by direct request to:
njha...@optusnet.com.au

"Norman Harker" <njha...@optusnet.com.au> wrote in message
news:#hixI1lvCHA.440@TK2MSFTNGP12...

Harlan Grove

unread,
Jan 17, 2003, 3:53:14 PM1/17/03
to
"Norman Harker" wrote...
..

>3. It's not good technique to have more than one name for the same cell or
>range. . . .
..

As long as one knows what one's doing, this is untrue without qualification. As
in real programming languages, there are occasions in which it's handy to have
multiple aliases for the same variable (ranges, in Excel). Only if the two names
could/would be used interchangeably would it be questionable to have multiple
names.

--
Public Service Announcement
Don't attach files to postings in nonbinary newsgroups like this one.

Harlan Grove

unread,
Jan 17, 2003, 4:05:21 PM1/17/03
to
"Norman Harker" wrote...
..

>And here's a best kept secret of Excel but it comes with a health warning.
>
>Tools > Options > Transition
>Check Transition Navigation Keys
>
>Now:
>/RNR
>
>Removes every single name in the workbook!
>
>Beats doing them one at a time although you can use VBA or various Add-Ins.
..

Dangerous to enable this. Better, if more recent Excel versions still support
it, to use Help > Lotus 1-2-3 Help... in Demo mode, and select Range > Name >
Reset, or [Alt]+H, L, R, N R. This is still live in Excel 97.

Harlan Grove

unread,
Jan 17, 2003, 5:34:53 PM1/17/03
to
"Harlan Grove" wrote...

>"Norman Harker" wrote...
>..
>>And here's a best kept secret of Excel but it comes with a health warning.
>>
>>Tools > Options > Transition
>>Check Transition Navigation Keys
>>
>>Now:
>>/RNR
>>
>>Removes every single name in the workbook!
>>
>>Beats doing them one at a time although you can use VBA or various Add-Ins.
>..
>
>Dangerous to enable this. Better, if more recent Excel versions still support
>it, to use Help > Lotus 1-2-3 Help... in Demo mode, and select Range > Name >
>Reset, or [Alt]+H, L, R, N R. This is still live in Excel 97.

Never mind. Putting Excel into Lotus 1-2-3 demo mode enables Transition
navigation keys (at least in Excel 97). Not nice.

0 new messages