Excel

[chriswoo]

Is there a way to automate Naming cells in Excel ? For example, i want to name B2 a combination of A2 (North) and B1 (South) to name B2 NorthSouth. This would be for a worksheet with hundreds or rows and columns and I don't want to have to have to name them all manually ! Thanks for any time spent thinking about this.

 

[MJPPaba]

Tricky procedure but follow carefully...

1 in cells A2:A100 (can be any number) enter the row title as in your example for A2 North.

2 in Cell B1 enter your column title. (your data from cell B1 South)

3 In Cell B2 enter the following formula, =$A1&B$1
this will concatonate the two cell references and anchor the two cells.

4 Copy cell B2, paste into cells B3:B100

5 Select cells B2:C100

6 In the menu click on [Insert] - [Name] - [Create]

7 From the dialog select ensure there is a check mark in the left column option. Ensure also the other options are de-selected. Click [OK]

8 This has created the first column of named ranges.

9 Select cells B2:B100

10 Paste them on to cells C2:C100

11 Select cells C2100

12 Repeat stages 6-8 to name the second column of Data

13 Continue the loop of moving across one column and selecting the two columns of data to apply the left column as the name.

14 You may want to automate this with a Macro as well if like you say you have a lot of columns. Replay if you want help with this.

Cheers

Matthew Paba



The only bad question is the question you dont ask!

 

[SkipVought]

Chris,

Assuming that
[tt]
B1: South
A2: North
[/tt]
select A1:B2

Insert/Name/Create - Create names in... and select TOP & LEFT
[tt]
=North South
[/tt]
results in the VALUE in cell B2


Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}