Add data to and existing table using named ranges.

NWVR

New member
Joined
May 4, 2022
Messages
2
Reaction score
0
Points
0
Excel Version(s)
365
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")
RangeDescriptionRange NameData
A1Persons full nameA1 = HW_FullNameJohn Doe
A2Persons IDA2 = HW_ID12345
A3Persons AgeA3 = HW_Age20

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)
1Jeffrey Gold432430
2Angie Drew96443
3John Doe1234519
4Jessica Jones447425

Thanks.
 

Nick Burns

Member
Joined
May 24, 2017
Messages
153
Reaction score
0
Points
16
Excel Version(s)
Office 365
Could you provide a sample workbook with the sheets and tables?
 

NWVR

New member
Joined
May 4, 2022
Messages
2
Reaction score
0
Points
0
Excel Version(s)
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")
 
Top