Building and Maintaining a List in C#.NET

Painting QR in C#.NET Building and Maintaining a List

To function effectively, a list should have the following characteristics:

The top row should consist of labels, with each label describing the contents of the col-

Each column should contain the same kind of information. Each type of information that you want to be able to sort by, search on, or otherwise

The list should not contain blank rows or columns. Ideally, the list should occupy a worksheet by itself. If that s not possible, separate the

list from any other information on the worksheet by at least one blank row and column. Figure 28-1 shows an example of a seven-column list. Each column in this list records a particular kind of information a field, in database terminology. The top row holds unique field labels. We ve separated first and last names in case we want to extract last names only or use the first and last names separately in a mail-merge process. The list has no blank rows or columns and occupies a worksheet by itself. Note

28

Figure 28-1. Each column in a list should contain a particular kind of information and the first row should consist of labels describing the columns contents.

We ve used Excel s Freeze Panes command to lock the top row so it remains visible as we scroll downward through the list. Excel marks the pane boundary with a rule.

You can make the top row bold to set it off visually from the rest of the list. This is helpful to whoever uses your list (including yourself), but Excel doesn t require it. Because the first and second rows in the list in Figure 28-1 contain different kinds of information columns C and D , for example, have text in the top row and dates below Excel recognizes the top row as a header row. If you sort your list, Excel is smart enough to keep the header row in place while it rearranges the rest of the list. If you create a PivotTable Report from your list, Excel will use the labels in the top row as field names. 702

Column G in Figure 28-1 is a calculated column one whose values are derived from calculations performed on other values in the list. In this case, we re calculating the age of each staff member by dividing the difference between today s date and the staff member s birthdate by 365.25 (the number of days in a year) and then using the INT function to round down to the nearest integer. The formula for each cell in this column (other than the header) is:

=INT(TODAY()-Date of Birth)/365.25)

The formula is remarkable in several ways. First and most important, it uses the heading from column D Date of Birth to extract values from that column. Using column D s heading instead of explicit references to D2, D3, and so on, makes the formula easy to read and understand. We didn t have to define Date of Birth as a name for the range D:D (or any portion of column D). Because of the label in D1, Excel simply recognized that that text stood for as many rows of column D as were occupied by the list. Second, none of the formulas in column G had to specify the name of the person whose date of birth we needed. Excel is smart enough to get just the birthdate value from the current row, and it does this faithfully for every row of the table using the identical formula in each cell of the Age column. (Had our formula applied a statistical function to Date of Birth, Excel would have assumed that we were interested in the entire column of birthdates, not simply the date on the current row. Thus, for example, we could determine the birthdate of the youngest staff member by writing =MAX(Date of Birth). We could write this formula anywhere on the worksheet, including outside the current list.) Third, even though the space character is Excel s intersection operator (the formula =7:7 B:B, for example, returns Lionel, the value of the cell at the intersection of row 7 and column B), Excel accepts the column label Date of Birth without complaint and doesn t make the mistake of trying to find the intersection of a range named Date and another range named of. The label-based formula mechanism is friendly and accommodating that way. (The older rangenaming technology accessible via the Insert, Name, Define command is less so. Names defined in this way cannot use space characters.)