Data mapping one sheet to another

**ramserp** · 05-04-2010, 12:24 AM

Hi Friends,

I have a workbook containing 3 worksheet Countries, Country Code, Country Name respectively. The worksheet countrries contains list of country code and country names. The worksheet "country code" range H10 contains country code. If the range H10 contans country code as IN and it should display as India in cell of D5 in "country name" sheet. The values should from the "countries" worksheet.

ex: if cell h10 in the "country code" sheet contains the value USA it should display as "United States of America" in the cell D5 in the "country name" sheet.

Please find attached workbook for your reference and also find code below which I was wrote.

Please Login or Register  to view this content.

Please kindly provide me the solution.

Thanks & Regards
Ramesh

**JBeaucaire** · 05-04-2010, 01:28 AM

There's no need for a macro. Just put this formula in D5:

=INDEX(countries!B:B, MATCH('Country Code'!H10, countries!A:A, 0))

Or:

=VLOOKUP('Country Code'!H10, countries!A:B, 2, FALSE)

Or even:

=LOOKUP('Country Code'!H10, countries!A:A, countries!B:B
(works because your data is sorted in column A)

**ramserp** · 05-04-2010, 02:15 AM

Hi,

Thank you very much for replying my post.

I need do this through macro only. Here my code follows.

Please Login or Register  to view this content.

This is the my code, I am getting error in this code please correct and reply for me

Regards
Ramesh

**JBeaucaire** · 05-04-2010, 02:21 AM

Then the answer is still the same, just in VBA:

Please Login or Register  to view this content.

**ramserp** · 05-04-2010, 02:58 AM

Hi JBeaucaire ,

Thank you, It is working fine. I would be greateful, if you tell how to work with the same from different workbooks . Sorry for asking too many questions.

Thanks & Regards
Ramesh

**JBeaucaire** · 05-04-2010, 08:30 AM

Have you ever created a formula that referred to a different workbook? It looks something like this:

=[Store.xls]Sheet1!$A$1

See the part in red shows how the workbook name is included in the formula. You need only add that to your formulas.