Using Smartsheet Forms as multi lingual survey tool


I am using the forms function to collect data from people in many different countries. The information we are collecting is in binary, single select, and multi select formats. This information is being summarized in a separate sheet, where we are counting the instances of each response from the binary, single, and multi-select categories by country.

I would like to be able to translate the form to French, and to have the French and English forms populate the same sheet. Where I am running into problems is that I cannot edit the dropdown menu options in one form without it affecting the other. What I want to be able to do is have the options in English on one form and in French on the other. I would like to know if either of the following options are feasible:

(1) To create a numerical code for each of the drop down options (i.e. Yes=1 No=0, Apple=1 Pear=2, Banana=3, etc.), and to have the labels appear differently in each form (i.e. Yes/Oui No/Non, Apple/Pomme, Pear/Poire, Banana/Banane), but the cells are populated with the numbers only (i.e. 1,0,1,2,3) - This would be ideal.

(2) To create a list of dropdown options which includes both translations, but to hide the French versions from the English form and the English versions from the French form - This would be less than ideal but I could make it work.

I realize Smartsheet is not a true survey tool, but I am hoping to be able to continue using it without having to resort to complex formula building. I am open to alternative options that do not require this. Any advice is appreciated, even if it is to abandon the Smartsheet ship for an actual survey application. :)



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!