Create named ranges from Column and Row headers
Results 1 to 7 of 7

Thread: Create named ranges from Column and Row headers
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2008
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Create named ranges from Column and Row headers

    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

    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

  2. #2
    Board Regular ZAX's Avatar
    Join Date
    Jul 2012
    Location
    Range("A1")
    Posts
    715
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create named ranges from Column and Row headers

    I assumed the data is in B2 to a various row and column.
    Code:
    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
    - -={Laziness is the mother of invention!}=- -
    For some fun, read the jokes in the following thread: "Funny jokes folks"
    Or you can check my "Shapes and faces" thread.
    My Email is "Toughkid999@hotmail.com"

  3. #3
    New Member
    Join Date
    Oct 2008
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create named ranges from Column and Row headers

    ZAX

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

    Thanks again,

    Bill

  4. #4
    Board Regular ZAX's Avatar
    Join Date
    Jul 2012
    Location
    Range("A1")
    Posts
    715
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Wink Re: Create named ranges from Column and Row headers

    Quote Originally Posted by BillXL View Post
    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!
    - -={Laziness is the mother of invention!}=- -
    For some fun, read the jokes in the following thread: "Funny jokes folks"
    Or you can check my "Shapes and faces" thread.
    My Email is "Toughkid999@hotmail.com"

  5. #5
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create named ranges from Column and Row headers

    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

  6. #6
    New Member
    Join Date
    Oct 2008
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create named ranges from Column and Row headers

    Hi circledchicken

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

    Regards

    Bill

  7. #7
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create named ranges from Column and Row headers

    You're welcome!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •