Results 1 to 3 of 3

Thread: Add data to and existing table using named ranges.

  1. #1
    Neophyte NWVR's Avatar
    Join Date
    May 2022
    Posts
    2
    Articles
    0
    Excel Version
    365

    Add data to and existing table using named ranges.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have done extensive search and multiple tests to no avail.

    Let's assume the following...
    I have an Excel sheet ("HW") that I am using as a form. to capture details. Each respective Cell is Named So, A1 = HW_FullName, A2 = HW_ID, A3 = HW_Age etc...
    I have another sheet ("Agents"). This sheet has a Named Table (tbl_AgentData) and I would like to use the Named Ranges by Header for the table i.e. tbl_AgentData([FullName]).

    Now once the "HW" form is filled in I want to click on a Macro Enabled button to then populate "tbl_AgentData" with the respective data.

    Macro Button already created.

    Let say I now put "John Doe" in Cell A1 which is HW_FullName. John already exists in tbl_AgentData([FullName])

    Sheet ("HW")
    Range Description Range Name Data
    A1 Persons full name A1 = HW_FullName John Doe
    A2 Persons ID A2 = HW_ID 12345
    A3 Persons Age A3 = HW_Age 20

    I want to take the info using it's respective Range("name") above and populate the table below (tbl_AgentData) once I click on the Macro button.

    So basically, I want to find "John Doe" using tbl_AgentData([FullName]) and populate or amend the data that already exists in tbl_AgentData([ID]) and tbl_AgentData([Age])

    Sheet ("Agents") : Range = tbl_AgentData
    A
    (Fullname)
    B
    (ID)
    C
    (Age)
    1 Jeffrey Gold 4324 30
    2 Angie Drew 964 43
    3 John Doe 12345 19
    4 Jessica Jones 4474 25

    Thanks.

  2. #2
    Conjurer Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    152
    Articles
    0
    Excel Version
    Office 365
    Could you provide a sample workbook with the sheets and tables?
    Oh... by the way, YOU'RE WELCOME!

  3. #3
    Neophyte NWVR's Avatar
    Join Date
    May 2022
    Posts
    2
    Articles
    0
    Excel Version
    365
    Thanks for coming back to me.

    After receiving your reply I did a bit of playing around to see if I could get it right and figured it out.

    This is exactly what I was looking for.
    Code:
    Application.WorksheetFunction.XLookup(Range("HW_Fullname"), Range("tbl_AgentData[Fullname]"), Range("tbl_AgentData[ID]")) = Range("HW_ID")
    Application.WorksheetFunction.XLookup(Range("HW_Fullname"), Range("tbl_AgentData[Fullname]"), Range("tbl_AgentData[Age]")) = Range("HW_Age")

Tags for this Thread

Posting Permissions

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