Is it possible to have this automation

[hancemj]

I have a list of Countrys and the related Country Codes within a worksheet.
I need a formula or Macro that will allow the Country to be entered and then the Country Code that related to that country to automatically replace the Country. I am using excel 2010.
An example is that in H1 the user will enter Iraq, Then I need excel to change that to the corresponding country code.

Below is a short list of the codes and Country's



[TABLE="width: 289"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]AD[/TD]
[TD]Andorra[/TD]
[/TR]
[TR]
[TD]AE[/TD]
[TD]U.A.E.[/TD]
[/TR]
[TR]
[TD]AF[/TD]
[TD]Afghanistan[/TD]
[/TR]
[TR]
[TD]AG[/TD]
[TD]Antigua and Barbuda[/TD]
[/TR]
[TR]
[TD]AI[/TD]
[TD]Anguilla[/TD]
[/TR]
[TR]
[TD]ALB[/TD]
[TD]Albania[/TD]
[/TR]
[TR]
[TD]ALG[/TD]
[TD]Algeria[/TD]
[/TR]
[TR]
[TD]AM[/TD]
[TD]Armenia[/TD]
[/TR]
[TR]
[TD]AN[/TD]
[TD]Netherlands Antilles[/TD]
[/TR]
[TR]
[TD]AO[/TD]
[TD]Angola[/TD]
[/TR]
[TR]
[TD]AQ[/TD]
[TD]Antarctica[/TD]
[/TR]
[TR]
[TD]ARG[/TD]
[TD]Argentina[/TD]
[/TR]
[TR]
[TD]AS[/TD]
[TD]American Samoa[/TD]
[/TR]
[TR]
[TD]AT[/TD]
[TD]Austria[/TD]
[/TR]
[TR]
[TD]AU[/TD]
[TD]Australia[/TD]
[/TR]
[TR]
[TD]AW[/TD]
[TD]Aruba[/TD]
[/TR]
[TR]
[TD]AZB[/TD]
[TD]Azerbaijan[/TD]
[/TR]
[TR]
[TD]BA[/TD]
[TD]Bosnia-Herzegovina[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]Barbados[/TD]
[/TR]
[TR]
[TD]BC[/TD]
[TD]British Columbia - Canada[/TD]
[/TR]
[TR]
[TD]BD[/TD]
[TD]Bangladesh[/TD]
[/TR]
[TR]
[TD]BE[/TD]
[TD]Belgium[/TD]
[/TR]
[TR]
[TD]BF[/TD]
[TD]Burkina Faso[/TD]
[/TR]
[TR]
[TD]BG[/TD]
[TD]Bulgaria[/TD]
[/TR]
[TR]
[TD]BH[/TD]
[TD]Bahrain[/TD]
[/TR]
[TR]
[TD]BI[/TD]
[TD]Burundi[/TD]
[/TR]
[TR]
[TD]BJ[/TD]
[TD]Benin[/TD]
[/TR]
[TR]
[TD]BM[/TD]
[TD]Bermuda[/TD]
[/TR]
[TR]
[TD]BN[/TD]
[TD]Brunei Darussalam[/TD]
[/TR]
[TR]
[TD]BO[/TD]
[TD]Bolivia[/TD]
[/TR]
[TR]
[TD]BR[/TD]
[TD]Brazil[/TD]
[/TR]
[TR]
[TD]BS[/TD]
[TD]Bahamas[/TD]
[/TR]
[TR]
[TD]BT[/TD]
[TD]Bhutan[/TD]
[/TR]
[TR]
[TD]BV[/TD]
[TD]Bouvet Island[/TD]
[/TR]
</tbody>[/TABLE]

 

[tusharm]

Far better to see the country code in an adjacent cell. Use INDEX and MATCH.

Easier to understand, requires no code, and the functionality is obvious.

I have a list of Countrys and the related Country Codes within a worksheet.
I need a formula or Macro that will allow the Country to be entered and then the Country Code that related to that country to automatically replace the Country. I am using excel 2010.
An example is that in H1 the user will enter Iraq, Then I need excel to change that to the corresponding country code.

Below is a short list of the codes and Country's



[TABLE="width: 289"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]AD[/TD]
[TD]Andorra[/TD]
[/TR]
[TR]
[TD]AE[/TD]
[TD]U.A.E.[/TD]
[/TR]
[TR]
[TD]AF[/TD]
[TD]Afghanistan[/TD]
[/TR]
[TR]
[TD]AG[/TD]
[TD]Antigua and Barbuda[/TD]
[/TR]
[TR]
[TD]AI[/TD]
[TD]Anguilla[/TD]
[/TR]
[TR]
[TD]ALB[/TD]
[TD]Albania[/TD]
[/TR]
[TR]
[TD]ALG[/TD]
[TD]Algeria[/TD]
[/TR]
[TR]
[TD]AM[/TD]
[TD]Armenia[/TD]
[/TR]
[TR]
[TD]AN[/TD]
[TD]Netherlands Antilles[/TD]
[/TR]
[TR]
[TD]AO[/TD]
[TD]Angola[/TD]
[/TR]
[TR]
[TD]AQ[/TD]
[TD]Antarctica[/TD]
[/TR]
[TR]
[TD]ARG[/TD]
[TD]Argentina[/TD]
[/TR]
[TR]
[TD]AS[/TD]
[TD]American Samoa[/TD]
[/TR]
[TR]
[TD]AT[/TD]
[TD]Austria[/TD]
[/TR]
[TR]
[TD]AU[/TD]
[TD]Australia[/TD]
[/TR]
[TR]
[TD]AW[/TD]
[TD]Aruba[/TD]
[/TR]
[TR]
[TD]AZB[/TD]
[TD]Azerbaijan[/TD]
[/TR]
[TR]
[TD]BA[/TD]
[TD]Bosnia-Herzegovina[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]Barbados[/TD]
[/TR]
[TR]
[TD]BC[/TD]
[TD]British Columbia - Canada[/TD]
[/TR]
[TR]
[TD]BD[/TD]
[TD]Bangladesh[/TD]
[/TR]
[TR]
[TD]BE[/TD]
[TD]Belgium[/TD]
[/TR]
[TR]
[TD]BF[/TD]
[TD]Burkina Faso[/TD]
[/TR]
[TR]
[TD]BG[/TD]
[TD]Bulgaria[/TD]
[/TR]
[TR]
[TD]BH[/TD]
[TD]Bahrain[/TD]
[/TR]
[TR]
[TD]BI[/TD]
[TD]Burundi[/TD]
[/TR]
[TR]
[TD]BJ[/TD]
[TD]Benin[/TD]
[/TR]
[TR]
[TD]BM[/TD]
[TD]Bermuda[/TD]
[/TR]
[TR]
[TD]BN[/TD]
[TD]Brunei Darussalam[/TD]
[/TR]
[TR]
[TD]BO[/TD]
[TD]Bolivia[/TD]
[/TR]
[TR]
[TD]BR[/TD]
[TD]Brazil[/TD]
[/TR]
[TR]
[TD]BS[/TD]
[TD]Bahamas[/TD]
[/TR]
[TR]
[TD]BT[/TD]
[TD]Bhutan[/TD]
[/TR]
[TR]
[TD]BV[/TD]
[TD]Bouvet Island[/TD]
[/TR]
</tbody>[/TABLE]

 

[hancemj]

Thank You. I am currently already using an adjacent cell to populate the country code by using index and match. But boss man wants them to enter in a country name and the worksheet changes it to the country code.

 

[tusharm]

Tell your "boss man" to take a course in customer friendly service, one that includes concepts of customer friendly UI design.

He wants someone to enter "British Columbia - Canada" just to see it replaced with BC?

And, what if the "country" -- with the unique definition used to define a country in the table {grin} -- is misspelled? Does he want it replaced with nothing?

In any case, you may want to look at writing code in the worksheet's SelectionChange event procedure.

I would use a dropdown (data validation) to *select* the country but I am sure your boss man knows his customer better than I do.

Thank You. I am currently already using an adjacent cell to populate the country code by using index and match. But boss man wants them to enter in a country name and the worksheet changes it to the country code.