Validation

To make sure that the data entered into your research records are valid, you need some kind of validation. Without validation, users can enter whatever they want into a cell. After validation, there is some form of data entry checking on your spreadsheets.

Here is one way to do validation:

1. Type a list of valid entries on a separate sheet.

2. Assign a Name to the list (see Naming Cells on page 6).

3. Go to the cell(s) in need of validation.

4. Select Validation in the Data menu.

5. For Allow, select List.

6. For Source, select =myList (the equal sign creates a reference to the range so named!)

Figure 84: The dialog box for Data Validation can be set, among other things, to a List (and its Source), or to Custom (and its formula).

Let's say that you want only those values in cell Al that range between 1 and 100. The formula would be:

• =AND(A1>0, A1<=100).

Or perhaps, you want cell A1 to only accept values between the minimum and maximum of values in column B:

• =AND(A1>=MIN(B:B),A1=MAX(B:B)).

Sorting Records

Rule #1: A table of records is demarcated by an entirely empty row and an entirely empty column (empty cells are OK). So avoid empty rows and columns inside your table, for those would create TWO or more tables, which would sort independently.

Rule #2: In order to sort by a specific column, do NOT select the total column, for you would...