Custom data validation

So far, you could find a lot of texts on the web site “Excel Kitchenette” about data validation. Most of them were about lists, since this functionality is often used, and further the choice of data type, setting the text length, number range or date were mentioned … When activating the Data Validation option, there are also custom rules, where it is possible to enter a formula based on which the validation is being performed.

Navigation

We often come across Excel documents that have multiple reports found in several worksheets. If you want to find the report you want, click on the tab with the name of the corresponding worksheet. However, this is not always the optimal solution. In order to facilitate positioning on the desired report, we introduce links that we have attached to certain forms. That is, we introduce a navigation panel to navigate within the document.

Constants

Few Excel users know about array formulas and I believe they have found themselves in a situation where they need them at least once. They were mentioned in one of the previous “recipes”, and here you will be able to learn how to use them in combination with constants. Constants are values ​​inside curly braces, separated by a comma (or semicolon), and we use them for arithmetic operations within an array formula.

Dynamic lists

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.

Generic forms

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.