When you begin to create formulas in Calc, you soon realize that the wrong kind of data in a cell referenced by your formula can throw the results off. Calc does its best to interpret the data entered according to the formatting style of the cell. If you format the cell as a date type, Calc does its best to translate the input in that cell as a date. At times it is successful and other times not. Fortunately, Calc comes with validity options built in to help you design your spreadsheet in such a way as to give the user help with the input or even force them to enter the data type needed for the cell. In this article, we will look at the validity options and its capabilities. Sheet Setup Before we get to the validity options of Calc, we need to set up a spreadsheet where we will use the options. The spreadsheet is a time sheet for showing the hours worked during a week. The time sheet works well for our example because on a given day, the next time input must have a greater value than the previous entry.

Lorsque vous commencez à créer des formules dans Calc, vous réalisez vite qu'un mauvais type de données dans une cellule référencée par votre formule peut casser les résultats. Calc fait de son mieux pour interpréter les données saisies selon le style de mise en forme de la cellule. Si vous formatez la cellule comme une date, Calc fait de son mieux pour traduire le contenu de cette cellule en date. Parfois, il réussit, parfois pas. Heureusement, Calc est livré avec des options de validité intégrées pour vous aider à concevoir votre feuille de calcul de manière à donner aux utilisateurs de l'aide à la saisie, ou même les forcer à saisir le type de données nécessaire pour la cellule. Dans cet article, nous allons examiner les options de validité et leurs capacités.

Configuration de la feuille

Avant de passer aux options de validité de Calc, nous avons besoin de mettre en place une feuille de calcul où nous allons utiliser ces options. La feuille de calcul est une feuille de temps pour montrer les heures travaillées au cours d'une semaine. La feuille fonctionne bien pour notre exemple, car pour un jour donné, l'entrée de temps suivante doit avoir une valeur supérieure à l'entrée précédente.

We will start by creating our title and column headers. In cell A1, enter “Time Sheet”. On row 2, fill in columns A-F with the following titles: DAY, IN, OUT, IN, OUT, TOTALS. Now for the row headers. In cell A3, type in “Sunday”. Making sure that cell A3 is highlighted, click on the small square in the lower right corner of the highlighted cell. The cursor will become a crosshair. Drag down to A9. When you release mouse button, the rest of the weekday names are filled in. This happens because the days of the week are one of the default sort lists. You can see all the lists and add your own at Tools > Options > LibreOffice Calc > Sort Lists. Now let's format the cells. Select B3:E9, right-click, and select Format Cells. Click the Numbers tab. From the Category list box, select Time. If you normally use a 12-hour clock, select the “01:37 PM” option under format. However, for a 24-hour clock, select “13:37.” Click OK to save the options. Column F is the totals. We will need to select a time format, at least for the last one, that can go beyond 24 hours. Select cells F3:F10, right-click, select Format Cells. Again, on the Numbers tab, select the Time category, but for the format, select “876613:37.” This will allow for totals that are greater than 24 hours. Click OK to save the settings. If you haven't yet, you might want to save your sheet.

Nous allons commencer par créer le titre et les en-têtes de colonne. Dans la cellule A1, indiquez « Feuille de temps ». Sur la ligne 2, remplissez les colonnes A à F avec les titres suivants : JOUR, DÉBUT, FIN, DÉBUT, FIN, TOTAL. Maintenant, les en-têtes de ligne. Dans la cellule A3, indiquez « lundi ». Sélectionnez la cellule A3, et cliquez sur le petit carré dans le coin inférieur droit de la cellule en surbrillance. Le curseur devient une croix. Faites glisser vers le bas jusqu'à A9. Lorsque vous relâchez le bouton de la souris, les autres noms de jours sont remplis. Cela fonctionne car les jours de la semaine sont l'une des listes de tri par défaut. Vous pouvez voir toutes les listes et ajouter les vôtres dans Outils > Options > LibreOffice Calc > Listes de tri.

Maintenant, formatons les cellules. Sélectionnez B3:E9, faites un clic-droit et sélectionnez Formatter les cellules. Cliquez sur l'onglet Nombres. Dans la liste Catégorie, sélectionnez Heure. Si vous utilisez habituellement une horloge sur 12 heures, sélectionnez l'option « 01:37 PM » comme format. Pour une horloge sur 24 heures, sélectionnez « 13:37 ». Cliquez sur OK pour enregistrer les options. La colonne F contient les totaux. Nous aurons besoin de choisir un format d'heure, au moins pour la dernière, qui peut aller au-delà de 24 heures. Sélectionnez les cellules F3:F10, faites un clic-droit, sélectionnez Formatter les cellules. À nouveau, sur l'onglet Nombres, sélectionnez la catégorie Heure, mais pour le format, sélectionnez « 876613:37 ». Cela permettra d'avoir des totaux dépassant 24 heures. Cliquez sur OK pour enregistrer les paramètres.

Si vous ne l'avez pas encore fait, vous pouvez enregistrer votre feuille de calcul.

Setup Validation We will need to check the validity of the time inputs. We will want them in a Calc-acceptable time format. This is the only requirement for valid data in the B column. For the other columns (C-E), we will need to also make sure the input is greater than the input before it. Select cell B3, then Data > Validity. The Validity dialog will appear. The Criteria tab (above) is where we control what constitutes valid data. The Allow drop-down list defines the type of data that is acceptable. The default is All Values. The other types are Whole Numbers, Decimal, Date, Time, Cell Range, List, and Text Length. The choices for the different types are basically the same except for Cell Range and List. With the cell Range, you define a cell range and the cell must contain a value that is in one of those cells. The defined range must consist of only one column or one row. With the List option, you define a list of acceptable values. With both the List and Cell Range options, you get a drop-down list box with the acceptable values. For our purposes, we need Allow set to Time.

Validation de la configuration

Nous aurons besoin de vérifier la validité des saisies d'heures. Nous les voulons dans un format acceptable pour Calc. C'est la seule exigence pour des données valides dans la colonne B. Pour les autres colonnes (C à E), nous aurons également besoin de vérifier que la saisie est supérieure à la saisie précédente.

Sélectionnez la cellule B3, puis Données > Validité. La boîte de dialogue de validité apparaît.

L'onglet Critères (ci-dessus) est l'endroit où nous contrôlons ce qui constitue des données valides. La liste déroulante Autoriser définit le type de données qui est acceptable. La valeur par défaut est « Toutes les valeurs ». Les autres types sont Nombres entiers, Décimal, Date, Heure, Plage de cellules, Liste, et Longueur du texte. Les choix pour les différents types sont fondamentalement les mêmes, sauf pour Plage de cellules et Liste. Pour Plage de cellules, vous définissez une plage de cellules et la cellule doit contenir une valeur qui est dans une de ces cellules. La plage définie doit être composée d'une seule colonne ou une ligne. Avec l'option Liste, vous définissez une liste de valeurs acceptables. Avec les options Liste et Plage de cellules, vous obtenez une zone de liste déroulante avec les valeurs acceptables. Pour nos besoins, nous devons régler Autoriser sur Heure.

Check the box for “Allow empty cells” to allow cells to remain empty. This will allow us to start with a blank time sheet. The Data drop-down list allows you to select the comparative operator to use for this validation. The choices are equal, less than, greater than, less than or equal to, greater than or equal to, not equal, valid range, and invalid range. Each one asks for a minimum, maximum, or value to compare, except for the two range choices. The range choices require a minimum and maximum. For our purpose, we need the greater than operator. In the minimum text box, enter 12:00 AM or 00:00. Click on the Input Help tab (below). The settings on this tab are optional. The idea is to provide the user with information about what to enter into the cell as a tip box that pops up when the cell is selected. To activate, click the checkbox for “Show input help when cell is selected.” Create a title for the tip box by typing it in the Title text box. For us, we could use the title “Start Time”. The Input Help text box is where you put the actual help tip like “Enter the start time: ex. 1:00 PM or 13:00”.

Switch to the Error Alert tab (above). Here we can set the validation to display a message when invalid data is entered. To activate the setting, check the checkbox for “Show error message when invalid values are entered”. There are four choices for the Action drop-down list, Stop, Information, Warning, and Macro. Stop, the one we will use, prompts with a dialog and rejects the input when invalid data is entered. For Information and Warning, they prompt with a dialog containing OK and Cancel buttons when invalid data is entered. If the user clicks OK, the input is left as it is. If the user clicks Cancel, the input is rejected. Macro will allow you to select a macro to run when invalid data is entered. The Title and Error Message is the title and message for the dialog box. We could set them to “Invalid Time” and “Enter in time format: 13:00 or 1:00 PM”. Once you have everything set, click OK to save the Validity dialog settings.

We will copy cell B3 to cells B4:B9. Right-click cell B3 and select Copy. Select cells B4:B9, right-click, and select Paste. This will copy the validity settings to the cells. The whole column will have the same requirements for input. For columns C, D, and E, we will do almost the same thing. We will go through the steps on C, and you can repeat for D and E. Select cell C3, Data > Validity. On the Criteria tab of the Validity dialog, select Time from the Allow drop-down list. Check to allow empty cells. Set Data to greater than, and set the minimum value to B3. Notice that we use a relative cell reference here. This will allow us to copy it to the other cells and have the reference translate. These criteria tell Calc that the cell requires a value greater than the value in cell B3. Do what you want with the help and error tabs. I do recommend using at least Information or Warning messages for invalid data. The messages could mention needing a greater value (or a later time). Right-click cell C3 and copy the cell, then paste it into cells C4:C9. You can repeat for columns D and E. You can also just copy C3 to D3:D9 and E3:E9, but you will need to adjust the text in the dialogs for help and error.

Total Formulas Now that we have our validation set up, we need to create the formulas to give us our totals. We will start with the daily totals. Select cell F3. Enter the formula =(C3-B3)+(E3-D3) which will give us the total for Sunday's times. Right-click F3 and Copy. Select cells F4:F9, right-click, and Paste. For the weekly total in F10, we will just sum the daily totals. Here is the formula: =SUM(F3:F9)

Protect Cells Once you have a sheet set up the way you like, you may want to protect the parts of the sheet you don't want changed like the labels and formulas. First, you need to tell Calc whether the cell is protected or not. Then you add protection to the sheet or document once everything is finished. In the case of our time sheet, we need to protect everything except for the cells where the user enters their times, B3:E9. By default, the Protect option is turned on for all cells. To change the protection setting for the input cells, select B3:B9, Format > Cells, and the Format Cells dialog appears. Click on the Cell Protection tab and uncheck Protected.

At this point, you can still edit any cell because the sheet or document is not protected. To turn on protection for the sheet or document, Tools > Protect Document > Sheet (or Document). The Protect Sheet or Protect Document dialog will appear depending on which option you selected. Enter and confirm a password in the dialog. The options will allow you to check whether the user can select protected or unprotected cells. If you do not enter a password, then no password is required to remove protection from the sheet or document. To remove protection from a sheet or document, Tools > Protect Document > Sheet (or Document). If a password was used when protection was set, enter the password used to protect the sheet or document.

Mark Invalid Data with Detective If you selected Warning or Information from the Action drop-down list in the Validity dialog, a user could enter invalid data and click OK to keep the invalid input. This may generate strange results in your totals or an error. Tools > Detective > Mark Invalid Data will mark the invalid data. Once the user corrects the data, Tools > Detective > Mark Invalid Data should clear the marks, unless the data is still invalid. Notice from my sample that invalid data can cause other cells data to show as invalid. The Calc Validity options are a great way to set up sheets for use by other people, or to keep you from entering invalid data on a complicated sheet. You can set it up to reject the invalid data outright, or for the user to make a choice about keeping the input. Once you have the sheet set the way you want it, you can protect the sheet from unwanted changes. This is a good way to protect formulas and labels. Finally, the Detective tool allows the user to mark invalid data they entered.