Is it possible to have Excel automatically use a row AND column title to name a cell?

[AccountsHIS]

Hello -

I have a large spreadsheet with a list of 170 names in column A, and about eight titles in row 1. I would like to name each of the cells (I link to them from another spreadsheet, and this way I can add or remove rows without messing up my linked sheet) with 'Name 1_Title1' 'Name 1_Title 2' etc. I have tried using 'create from selection' in the name manager, choosing both left column and top row, but this doesn't seem to work (perhaps I'm missing a step?). I would love to hear any suggestions for this, as naming each cell manually is very time consuming. Thanks for the help!

[Tony Valko]

It depends on what version of Excel you're using.

This works for me in Excel 2002:

Data Range

	A	B	C	D
1		Col1	Col2	Col3
2	Row1	51	26	9
3	Row2	96	63	96
4	Row3	15	84	92
5
6	63
7

Using the defined row and column labels as references.

Formula entered in A6:

=Row2 Col2

However, I can't get it to work in any of the other versions of Excel that I have (2007, 2010, 2013).

[Jonmo1]

Works for me as well using XL2010
However, it added _ to the end of each name, Row1_ Row2_

so formula is

=Row2_ Col2_

Returns 63

[Jacc]

How about using the Excel Table feature? It's under Insert, to the left.
http://www.techrepublic.com/blog/10-...-table-object/
http://www.excel-easy.com/examples/s...eferences.html

Or if you want to be old fashion I believe you can do some neat things by naming an entire column and then use the intersect function.
http://trumpexcel.com/2013/08/inters...ator-in-excel/
http://www.excelcodex.com/2012/05/ex...-intersection/

Not much info I can give you unless you post a sample workbook.

[Jonmo1]

However, it added _ to the end of each name, Row1_ Row2_

And just figured out why...

Col1 and Row1 are already valid ranges, Row 1 Column COL and ROW
Excel 2007+ has columns all the way out to XFD

So the name cannot be Row1 or Col1, so it added the _ characters.

[Tony Valko]

Yeah, I should have caught that.

I got a #NULL! error when I tried it in Excel 2007 as:

=Row2 Col2

[Tony Valko]

Ok, it does work in all the versions of Excel that I have:

Tested in Excel 2007, 2010 and 2013.

Data Range

	A	B	C	D
1		Region1	Region2	Region3
2	Sales1	6	58	5
3	Sales2	14	77	96
4	Sales3	69	84	98
5
6	96

=Sales2 Region3

It also works in the opposite direction:

=Region3 Sales2

[AccountsHIS]

Thanks for all the replies! Unfortunately none of the suggestions seem to work for what I want, and I'm wondering if I should be doing something else altogether. I am attaching two files that represent the ones I'm working with. Information is added and changed in the Tuition_list file, and that informs the second file (Tuition invoicing guide). Students are occasionally added to the first file, so I need to be able to add a row in my invoicing guide without throwing everything off (including my row shading). Naming each cell in the Tuition_list file works for this purpose, but it is very labour intensive, and has to be redone each year (when I create a new sheet). Is there a better way to link the cells? I was hoping the 'Create from Selection' in the Name Manager could name them all for me, but I haven't been able to get that work (the cells remain named with the column & row #). Any suggestions are greatly appreciated. Thanks!