Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

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
0
rutgermons
Asked:
rutgermons
  • 6
  • 6
  • 2
1 Solution
 
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
 
rutgermonsAuthor Commented:
macro will do thanks
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
RobOwner (Aidellio)Commented:
file attache
CASE.xlsx
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 6
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now