lookup values from 1 field/sheet and set values in other sheet

Folks


I need a code that based on a value in field A2 (Sheet 1) goes and looks up a value in column A ( sheet 2 ) and set's the value of that exact
opposite row/col field in sheet 2 in column b to a specific value


i.e.

if

Sheet 1 a2 = 'germany'

then look in column a in sheet 2 for values "capital" ,"currency", "language" and set the value on sheet 2 in the opposite colum to

"berlin","euro","german"


Sheet 1 a2 = 'italy'

then look in column a in sheet 2 for values "capital" ,"currency", "language" and set the value on sheet 2 in the opposite colum to

"rome","euro","italian"


Sheet 1 a2 = 'usa'

then look in column a in sheet 2 for values "capital" ,"currency", "language" and set the value on sheet 2 in the opposite colum to

"washingtondc","dollar","english"

attached a file sample where germany was selected
rutgermonsAsked:
Who is Participating?
 
RobConnect With a Mentor Owner (Aidellio)Commented:
file attache
CASE.xlsx
0
 
Danny ChildIT ManagerCommented:
I don't think your example has attached properly?
0
 
Danny ChildIT ManagerCommented:
And! Do you need a macro to do this, or just vlookups?
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
rutgermonsAuthor Commented:
macro will do thanks
0
 
RobOwner (Aidellio)Commented:
Can you please attach the sample file.
0
 
rutgermonsAuthor Commented:
see attached
0
 
rutgermonsAuthor Commented:
0
 
RobOwner (Aidellio)Commented:
Where are the details of each country stored?  It can't just pull this from thin air.

I type "Australia" in A2... where does the details for sheet2 come from?  Are they hardcoded, on another sheet, or in a database somewhere?

You may be able to use the VLOOKUP function or array formulas if you have this info in the spreadsheet
0
 
rutgermonsAuthor Commented:
Hi Rob

they are hardcoded (just to keep it simple)
0
 
RobOwner (Aidellio)Commented:
Can you post an example of how they are hardcoded and i'll knock up a formula for you
0
 
rutgermonsAuthor Commented:
rob, i just think 2 colums will be sufficient i.e. if captil =berlin in a2 b2 respectively
0
 
RobOwner (Aidellio)Commented:
Ok i've knocked up a simple demo

on Sheet 2 you can select a country from a drop down list, which will automatically populate the table below it
0
 
rutgermonsAuthor Commented:
OK, would be keen to see if you can share it

cheers
0
 
RobOwner (Aidellio)Commented:
Haha I always laugh at those that forget to attach the file :-[ . I'll post it soon
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.