Solved

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

Posted on 2013-12-30
14
272 Views
Last Modified: 2014-01-12
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
Comment
Question by:rutgermons
  • 6
  • 6
  • 2
14 Comments
 
LVL 23

Expert Comment

by:DanCh99
Comment Utility
I don't think your example has attached properly?
0
 
LVL 23

Expert Comment

by:DanCh99
Comment Utility
And! Do you need a macro to do this, or just vlookups?
0
 

Author Comment

by:rutgermons
Comment Utility
macro will do thanks
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
Comment Utility
Can you please attach the sample file.
0
 

Author Comment

by:rutgermons
Comment Utility
see attached
0
 

Author Comment

by:rutgermons
Comment Utility
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:rutgermons
Comment Utility
Hi Rob

they are hardcoded (just to keep it simple)
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
Comment Utility
Can you post an example of how they are hardcoded and i'll knock up a formula for you
0
 

Author Comment

by:rutgermons
Comment Utility
rob, i just think 2 colums will be sufficient i.e. if captil =berlin in a2 b2 respectively
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
Comment Utility
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
 

Author Comment

by:rutgermons
Comment Utility
OK, would be keen to see if you can share it

cheers
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
Comment Utility
Haha I always laugh at those that forget to attach the file :-[ . I'll post it soon
0
 
LVL 42

Accepted Solution

by:
Rob Jurd, EE MVE earned 500 total points
Comment Utility
file attache
CASE.xlsx
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now