The near universality of Excel means that it is often used as a form to capture data and input it to database systems. Excel files can be easily circulated and though InfoPath is a better tool for this job, the familiarity of Excel often leads to it being used instead of InfoPath forms.
To get anywhere near to the functionality available out-of-the-box using InfoPath, some pretty tortuous tricks need to be used in Excel, using data validation and named ranges. This post tries to capture some of the techniques I used recently at a client to make Excel a better tool for data capture.
In the scenario we faced, an international organization is using Excel as an input form to gather information from a range of partner organizations around the world. The institution has to work in six official languages, but for the most part, the interface in Excel has to exist in three principal languages, English, French and Spanish. Alongside the language choice, the organization collects information from three main sectors of activity; let’s call them Services, Industry and Agriculture, for the sake of argument. Now though a number of users are now moving to Office 2010 and the though the norm is still Office 2007, the institution has to cater to the lowest common denominator, which remains the Excel 2003 binary file format. In addition, a number of partner organizations cannot use macro-enabled spreadsheets and so the requirement was for an Excel interface which allowed for the easy switching of languages and that the contents of any drop-downs boxes should reflect the language choice which had been made, all done with only formulas and standard Excel functionality.
For the language choice, we set up a cell with data validation, based on a list containing the values English, Espagnol and Français as follows.
The cell was then given a Name, Language as follows:
Translations for headings are put in a separate worksheet called Translations, which will later be hidden from users. Headings in English are put in row 2, the equivalent translation in Spanish put in row 3 and that for French in row 4, as the mock-up below demonstrates:
A formula is used with nested IF statements to display the headings in the form, dependent on the language chosen.
The full formula is shown below:
=IF(Language=”English”,Translations!B2,IF(Language=”Espagnol”,Translations!B3,IF(Language=”Français”,Translations!B4)))
So far, so good and the technique provides a very simple interface which allows labels to be switched by the user at their own convenience.
However, the form must also contain drop-down boxes with suggested values under certain columns, and these choices must also change with reference to the language selected and also be constrained with reference to another drop-down we will call Sector.
First we will create the translations for the choices available in the drop-down box in the different languages:
The first column contains a Named Range called Agriculture covering the 7 rows of choices that are then provided in the next three columns in English, Spanish and French. For each cell in the Agriculture Named Range, a formula calculates the contents according to the Language chosen:
=IF(Language=”English”,B8,IF(Language=”Espagnol”,C8,IF(Language=”Français”,D8)))
In this way, the appropriate contents of the drop-down box are shown, relating to both Language and Sector selected.The same technique is used for the other two Sectors, Industry and Services.
These techniques are relatively simple but the end result is a more powerful multilingual form, with data validation but no use of macros.
Highbrowse 