rutgermons
asked on
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","e nglish"
attached a file sample where germany was selected
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","e
attached a file sample where germany was selected
I don't think your example has attached properly?
And! Do you need a macro to do this, or just vlookups?
ASKER
macro will do thanks
Can you please attach the sample file.
ASKER
see attached
ASKER
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
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
ASKER
Hi Rob
they are hardcoded (just to keep it simple)
they are hardcoded (just to keep it simple)
Can you post an example of how they are hardcoded and i'll knock up a formula for you
ASKER
rob, i just think 2 colums will be sufficient i.e. if captil =berlin in a2 b2 respectively
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
on Sheet 2 you can select a country from a drop down list, which will automatically populate the table below it
ASKER
OK, would be keen to see if you can share it
cheers
cheers
Haha I always laugh at those that forget to attach the file :-[ . I'll post it soon
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.