So far, you have been able to read several “recipes” on this blog which were about Data Validation and lists. Office365 (Microsoft365) subscription users have access to dynamic array functions that easily handle variable-length lists. But what should users with an older version of Excel do? In the text that follows, you will learn how to solve this problem with the help of the named ranges, OFFSET and COUNTA functions.
Excel has, within the VBA Editor, a form design tool that helps you easily fill in structured data tables. However, some of you still do not know the VBA and are unable to create the form yourself. Some others will find themselves in the situation that their job is overwhelmed by entering data one time. For all of you, in the “recipe” that follows, I will show how to enter data in a table using generic Excel forms.
Excel offers you to add controls to documents, the same elements that you can find in windows and dialog boxes, and they enable choosing values, checking options, adding labels, choosing from list objects… Adding control to a worksheet increases interactivity between user and reports. In this way, you can easily create different polls and save the results in a form suitable for further processing.
Although it has not been initially made as a translation tool (let’s face it, most of us use Google Translate) Excel has several features that allow you to translate table names from one language to another, check the spelling and grammar, and, if possible, replace one of the labels with a synonym. It’s rare for anyone to know that these options exist, or at least never used them, so it is not bad to mention them.
Fuzzy Lookup is an Excel add-in created by a Microsoft development team, and can be used for advanced search and comparison of column values in tables. Why does this serve for? For example, in practice, it often happens that, when entering a name and surname, an error occurs or there is no one-way entry. Fuzzy Lookup allows you to compare the column based on the similarity and display the corresponding results in the new table.