Create named ranges from Column and Row headers

[BillXL]

Is there any way in Excel to create single-cell named ranges from a combination of the labels in the rows and column of a spreadsheet.

Here is an example:

	CompanyA	CompanyB	CompanyC	CompanyD
Sales	100	200
Cost	300
Profit

<tbody>
</tbody>

I would like the first cell (containing the 100) to have a defined name of (something like) “SalesCompanyA”, and the second cell to be “SalesCompanyB”. And so on – e.g. the cell with 300 in it should be “CostCompanyA”.

I can do it manually, but I have a huge spreadsheet & was hoping it could be automated. Just to be clear, it needs to be a single cell range. I know you can create a range from a selection – but this seems to create ranges of the entire row and/or column.

Many thanks

Bill

 

[ZAX]

I assumed the data is in B2 to a various row and column.

Sub NameCells()
Col = Cells(1, Columns.Count).End(xlToLeft).Column
Row = Cells(Rows.Count, 1).End(xlUp).Row
For Each Cell In Range(Cells(2, 2), Cells(Row, Col))
    ActiveWorkbook.Names.Add Cells(Cell.Row, 1).Value & Cells(1, Cell.Column).Value, Cell.Address
Next
End Sub

Plz,Try it on a sample data first!

ZAX

 

[BillXL]

ZAX

That is brilliant - thank you so much. It does exactly what I need.

Thanks again,

Bill

 

[ZAX]

ZAX

That is brilliant - thank you so much. It does exactly what I need.

Thanks again,

Bill

You're welcome,Glad to get it to work!

 

[circledchicken]

Hi,

Another alternative here rather than creating named ranges for every cell in the table is to create names based on the row and column headers (highlight the range and create from selection using top row and left column), and then use the intersection operator (space). Then you can use a formula like:

=Sales CompanyA

to return 100.

For more on the intersection operator and a short video showing exactly how to do this see:
How to write 2 Way Lookup Formulas in Excel? | Chandoo.org - Learn Microsoft Excel Online

 

[BillXL]

Hi circledchicken

I'm amazed! I have never seen that functionality before. Very useful - thank you.

Regards

Bill

 

[circledchicken]

You're welcome!