Excel 2007 naming multiple cells

Microsoft Office excel 2007
July 22, 2011 at 04:26:39
Specs: Windows XP
In Excel 2007, I have set up a grid of cells. There is a header row at the top of the grid with labels c1to c10, and a column along the left side of the grid labelling the rows r1 to r10.

I wish to name all the individual cells in the grid using the row and column labels. I have read that this can be done in an earlier version (Excel 2003?) using Insert > Name > Create. I have found no such option in Exel 2007.

How can this be achieved in Excel2007?


See More: Excel 2007 naming multiple cells

Report •

July 22, 2011 at 10:19:06
In excel 2007 goto the formulas tab and look for the Defined Name section. Look for "Create from Selection"

if c1 to c10 are in cells B2 through K10 and r1 to r10 are in cells A3 through A12 you would need to highlight/select A2 to K12. then the "Create from slection" then check off top row and left column.

Problem: This method seems to only create names for the entire row or colum of the range minus the cell the header was in. if you are actually looking for a defined name for the individual cell, what I have seen so far will not work. you would need to manually name each cell.

The other problem is poor choice of heading names as they violate defined name syntax. As a result the defined names that get created will automatically be prefixed with "_"

here is a partial copy of syntax rules:

The following is a list of syntax rules that you need to be aware of when you create and edit names.

Valid characters The first character of a name must be a letter, an underscore character (_), or a backslash (\). Remaining characters in the name can be letters, numbers, periods, and underscore characters.
Note You cannot use the uppercase and lowercase characters "C", "c", "R", or "r" as a defined name, because they are all used as a shorthand for selecting a row or column for the currently selected cell when you enter them in a Name or Go To text box.

Cell references disallowed Names cannot be the same as a cell reference, such as Z$100 or R1C1.

Report •

July 22, 2011 at 20:48:54
re: "I wish to name all the individual cells in the grid using the row and column labels."

What do you want the names of the cells to be? Something like:

r1c1, r2c3, etc?

As Kitty mentioned, you'll probably need to start those names with an underscore.

_r1c1, etc. or myr1c1, etc. or something like that since r1c1 is not a valid name.

That said, this code will name each cell in the following grid by adding an underscore as the first character. e.g. the cell that contains H will be named _r3c2

Modify as required...

    A     B     C      D       
1         c1    c2     c3
2  r1     A     B      C
3  r2     D     E      F
4  r3     G     H      I

Sub CreateMyNames()
 For Each myCell In ActiveSheet.Range("B2:D4")
   ActiveWorkbook.Names.Add _
      Name:="_" & Cells(myCell.Row, 1) & Cells(1, myCell.Column), _
      RefersTo:="=" & ActiveSheet.Name & "!" & myCell.Address
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

Ask Question