How to create a reference variable in excel

Question

Okay I might've not explained it all that well in the title but bear with me.

I have an excel sheet with multiple list dropdowns. These dropdowns are filled with information that I use to generate a URL. The data in the dropdowns change the route parameters of said URL.

This generator is to be used by a marketing team. People who aren't familiar with the complicated codes that are passed as route parameters. I want to make the generator more user friendly.

I want the dropdowns to have understandable values. Such as for the language dropdown to say:

• English
• German
• French

But when select I want these items to have a different value. Like so:

• English = en-GB
• German = de-DE
• French = fr-FR

Is there a function to convert cell data to say something different? Or create an excel variable?

Answer 1

Robin's comment points the way. No need for VBA coding - you can use Tables, Data-Validation & VLOOKUP formula here.

1. Insert a Table over your languages and their corresponding codes (Insert --> Table).
2. I've named my Table TblLang, instead of the default "Table1" (overtype the name directly in the top-left text box on the Ribbon when on the Table Tools>Design tab)
3. Add a Data Validation, list-type to your user-selected cells. This is my col H, whereby the user can only pick a drop-down value that's in the Language table values. (Found under Data-->Data Validation...)
4. My forumla for the URL is: ="\\http:\myurlpath\ %lang=" & VLOOKUP(H4,TblLang,2,FALSE)

The formula look's up the value the user has picked from the drop-down cell in the table and returns the exact matched value from column 2, the code.