Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
shadestreet
 
Posts: n/a
Default Can I change the names of multiple cells at once?


I need to change the names of cells in an entire column. Instead of A1,
A2, A3... I want to have "hours1, hours2, hours3, hours4...

Is this possible? How?


--
shadestreet
------------------------------------------------------------------------
shadestreet's Profile: http://www.excelforum.com/member.php...fo&userid=7092
View this thread: http://www.excelforum.com/showthread...hreadid=546736

  #2   Report Post  
Posted to microsoft.public.excel.misc
SamuelT
 
Posts: n/a
Default Can I change the names of multiple cells at once?


Type 'hours1' in A1, and 'hourse2' in A2. Then just drag it down. Excel
should automatically do the rest for you.

SamuelT


--
SamuelT
------------------------------------------------------------------------
SamuelT's Profile: http://www.excelforum.com/member.php...o&userid=27501
View this thread: http://www.excelforum.com/showthread...hreadid=546736

  #3   Report Post  
Posted to microsoft.public.excel.misc
shadestreet
 
Posts: n/a
Default Can I change the names of multiple cells at once?


Your solution works only if I wanted to change the data in the cells,
but it is the actual name of the cell I want to change. I don't want
the cell to be called "A1", I want to refer to it as "hours1". I need
to change all cell names in the column, not the actual data.

Purpose is to write my formulas using the new names, so other users can
read the formulas and make sense of them.

(a formula that says "=widgets2/hours2" makes more sense than "=A2/B2")


--
shadestreet
------------------------------------------------------------------------
shadestreet's Profile: http://www.excelforum.com/member.php...fo&userid=7092
View this thread: http://www.excelforum.com/showthread...hreadid=546736

  #4   Report Post  
Posted to microsoft.public.excel.misc
SamuelT
 
Posts: n/a
Default Can I change the names of multiple cells at once?


I guess, depending on how many cells you're actually using, that you
could click on each cell you're using and rename it in the cell name
box in the top left hand corner.

If you've got a lot of cells though it's not really a viable solution.

SamuelT

PS - you wrote: a formula that says "=widgets2/hours2" makes more sense
than "=A2/B2". Not sure I agree with that ;)


--
SamuelT
------------------------------------------------------------------------
SamuelT's Profile: http://www.excelforum.com/member.php...o&userid=27501
View this thread: http://www.excelforum.com/showthread...hreadid=546736

  #5   Report Post  
Posted to microsoft.public.excel.misc
Gord Dibben
 
Posts: n/a
Default Can I change the names of multiple cells at once?

In B1 enter hours1

In B2 enter hours2

Select B1:B2 and drag/copy down as far as you wish.

Select Column A and B.

InsertNameCreateLeft Column and OK

The names hours1, hours2 stc. have been assigned to A1, A2 etc.

You can now "Clear Contents" of column B.

DO NOT "Delete" column B, just "clear contents".


Gord Dibben MS Excel MVP

On Tue, 30 May 2006 09:45:14 -0500, shadestreet
wrote:


I need to change the names of cells in an entire column. Instead of A1,
A2, A3... I want to have "hours1, hours2, hours3, hours4...

Is this possible? How?




  #6   Report Post  
Posted to microsoft.public.excel.misc
shadestreet
 
Posts: n/a
Default Can I change the names of multiple cells at once?


Gord, that tip is very helpful...

But I can't seem to get it to work... I followed your instructions
step-by-step

Is there something you left out or maybe an obvious mistake I am
making?


--
shadestreet
------------------------------------------------------------------------
shadestreet's Profile: http://www.excelforum.com/member.php...fo&userid=7092
View this thread: http://www.excelforum.com/showthread...hreadid=546736

  #7   Report Post  
Posted to microsoft.public.excel.misc
shadestreet
 
Posts: n/a
Default Can I change the names of multiple cells at once?


Never mind, I changed it to "right column" instead of "left column" and
it worked.

You mentioned that I should not delete column B, only clear the
contents. Just to test what happened I deleted it and everything seems
just fine, the names are still intact in column A. Is there some other
reason why you said not to delete column B?

Final question for anyone poking in this thread, Gord's tip is very
helpful, however, if I renamed the cells and then someone deletes a
row, Excel isn't smart enough to change the cell name reference. Is
there another method to achieve what I want where the cell names are
somehow updated?

Example, I rename A1, A2, and A3 "work1, work2, work3". If I delete
the 2nd row, the named cell "work3" is moved up to cell A2, and isn't
automatically renamed "work2", stays as work3.

Not too big of a problem, but would nice if this renaming could be
perfect.

Thanks


--
shadestreet
------------------------------------------------------------------------
shadestreet's Profile: http://www.excelforum.com/member.php...fo&userid=7092
View this thread: http://www.excelforum.com/showthread...hreadid=546736

  #8   Report Post  
Posted to microsoft.public.excel.misc
shadestreet
 
Posts: n/a
Default Can I change the names of multiple cells at once?


Well, I think I found the dealbreaker in my whole plan..

Apparently formulas that reference named cells do not automatically
update the reference if you drag the formulas down... stays fixed on
reference "work1" instead of "work2" when I drag the formula down one
notch.

Doubtful, but if there is a way around this final hurdle please let me
know.

Thanks


--
shadestreet
------------------------------------------------------------------------
shadestreet's Profile: http://www.excelforum.com/member.php...fo&userid=7092
View this thread: http://www.excelforum.com/showthread...hreadid=546736

  #9   Report Post  
Posted to microsoft.public.excel.misc
Dominic LeVasseur
 
Posts: n/a
Default Can I change the names of multiple cells at once?

You could probably use:

=indirect("work"&row())

HTH

"shadestreet" wrote:


Well, I think I found the dealbreaker in my whole plan..

Apparently formulas that reference named cells do not automatically
update the reference if you drag the formulas down... stays fixed on
reference "work1" instead of "work2" when I drag the formula down one
notch.

Doubtful, but if there is a way around this final hurdle please let me
know.

Thanks


--
shadestreet
------------------------------------------------------------------------
shadestreet's Profile: http://www.excelforum.com/member.php...fo&userid=7092
View this thread: http://www.excelforum.com/showthread...hreadid=546736


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Change scales from reference cells value for a chart in a separate sheet ers Charts and Charting in Excel 4 April 2nd 06 07:00 PM
apply cell names to formulas in multiple worksheets BBurrows Excel Worksheet Functions 4 July 1st 05 05:35 PM
Cannot change formats of cells??? ChrisA Excel Discussion (Misc queries) 2 June 27th 05 05:58 PM
write a function to determine if cells have names andrewm Excel Worksheet Functions 5 June 9th 05 07:28 PM
macro to change the names and delete closed books Tim Excel Discussion (Misc queries) 2 February 6th 05 10:39 PM


All times are GMT +1. The time now is 06:30 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"