allan_lee
asked on
Excel Worksheet Relationships
I have an Excel Sheet with two worksheets.
Worksheet1 and Worksheet2 are tables. I created a relationship between the two tables. I need to populate a column in Worksheet2 with data from Worksheet1 based on the relationship. See attached file for illustration.
Capture.PNG
Worksheet1 and Worksheet2 are tables. I created a relationship between the two tables. I need to populate a column in Worksheet2 with data from Worksheet1 based on the relationship. See attached file for illustration.
Capture.PNG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
BTW $A$2:$C$5 is the range of the data on Sheet1.
If the number of rows of data on Sheet1 may change over time then select the data and create a named range with the formula =OFFSET(Sheet1!$A$2,0,0,CO UNTA(Sheet 1!$A:$A),3 ). That will enable the named range to dynamically change with the number of data rows. Give it a name like LookupRange and then your formula on Sheet2 can be
=VLOOKUP(A2,LookupRange,3) .
=VLOOKUP(A2,LookupRange,3)
ASKER
Martin, I am getting an error saying "A Value is not available to the formula or function" when I try your formula.. Any more thoughts?
ASKER
After some more digging around, the error I was getting was due to a cells formatted as text.. After converting all the data in column A to number, the formula worked.. Thanks.
ASKER
Thanks.
You're welcome and I'm glad I was able to help.
If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2015