The data in my client’s file is confidential, so I’ll show you how this works using some text from a Honey Nut Cheerios box. Actually, this is how many of us learned some French – reading the back of the cereal box at the breakfast table!
Set up the Translation Table
In the sample Excel file, I set up a table with 3 columns – ID, English and French. I entered a few English and French phrases from the cereal box, and typed numbers for each row in column 1.
List the Languages
On the same sheet, I created a table with languages, and an ID number for each language. I selected cells G2:G3, with the language names, and named that range LangList.
Make a Language Selector
On the main worksheet, I created a data validation drop down list, based on the LangList range. Next, I named that cell – LangSel, and selected English as the language.
Get the Language ID
Back on the translation sheet, I added a formula to calculate the ID of the selected language. Here is the formula, in cell I2, which is named LangSelID:
When English is selected, the result is 1, and 2 for French.
Show the Selected Language
On the main sheet, I typed the numbers 1 to 5 in cells A4:A8
In cell B4, I entered an INDEX/MATCH formula, to pull the correct text from the translation table.
The INDEX function looks for a value in the English and French columns of the translation table. For the row, MATCH finds the number typed in column A, in the ID column of the translation table. For the column, it uses the LangSelID.
Change the Language
To see the French version of the text, select French in the LangSel drop down list. Now the LangSelID result is 2, so the text from the 2nd column of the English/French range is shown.
Link Shapes to Cells
In my client’s file, there are also a few flow charts, and I’ve linked those shapes to worksheet cells, so they can show text in the selected language too.
In the example shown below, the text box at the top of the graphic is linked to cell B4. There are two other text boxes, linked to cells B5 and B8. When French is selected, the diagram shows the text in that language.
Change the selected language to English, and the worksheet cells and the cereal box graphic also switch to English.
Download the Sample File
To see how the translation formulas work, you can download the sample file from my Contextures website. On the Sample Files page, go to the Functions section, and look for FN0029 – Show Text in Selected Language.
The zipped file is in xlsx format, and there are no macros in the file.