Quattro Pro provides a third method for filling a block of cells with consecutive values that is even easier than using formulas:
the Edit-Fill command. This command, however, requires four
CH.5
responses from you:
• The destination block that will receive the numbers. It can be any block of cells. The cells will be filled
column by column. The values will overwrite any data already in those cells, so be sure that the block you are filling does not have any existing cells that you don't want to lose.
• A start value (the number from which to begin in-crementing). This value can be any number or for-mula that returns a number as a result, or a cell address that contains a number (positive or negative, with or without decimals). This number will be placed in the first cell of the destination block.
• A step value (the number by which to increase each cell). This value can also be any number, formula, or cell address. Each cell will be this much greater (if the step value is positive) or less (if the step value is nega-tive) than the cell before it in the block .
• A stop value (the number at which to stop increment-ing). This value is the highest number you want in the filled block, or the lowest one if you are incrementing with a negative step value.
Here are the steps to follow to fill the same block as in the pre-vious example:
1. Invoke the Edit-Fill command.
2. Type in the address B1..BI000 as the destination block and press.J (that's right, 1000).
3. Enter 10 for the start value and press .J.
4. Type 10 for the step value and press .J.
5. Enter 150 for the stop value and press .J.
As soon as you press the final.J, the block of cells will be filled as you specified.
Why was such a large destination block chosen? In this case, you didn't care how many cells were in the block. The criteria was simply that the block should start in cell Bl, and that the numbers should start at 10, step by 10, and stop at 150. As soon as that value was reached, the command stopped filling, and the excess cells in the destination block were simply ignored.
There will be other times, however, when the size of the des-tination block is more important that the stop value. For ex-ample, if you have entered a column of data and now you want to number the rows from 1 to whatever the final row value would be. In this case, you would specifY the precise destination block, such as the cells in the column to the left of your data. The start value would be 1, as would the step value. But you may not really know what the stop value should be, nor do you care. It is the size of the block that will limit the fill. Simply specifY an overly large stop value, such as 99999, or accept the default stop value of8192 (which happens to be the number of rows in the spread-sheet). Your block will be filled to the last cell, but no farther, no matter what the final ending value is.
Here is a very convenient way to fill a block of cells with the dates that coincide with the first Monday of each week in the year. We'll use 1991 as the sample year and fill column A.
1. Invoke the Edit-Fill command.
2. SpecifY the block by typing in the address Al..AIOOO.
3. The start value will be the first Monday of 1991, which happens to beJanuary 7. Enter the formula
@DATE(91,1,7).
4. Enter 7 for the step value, which represents 7 days in a week.
5. The stop value will be the last day of the year. Enter
@DATE(91,12,31).
CH.5
(a)
Remember that deleting the upper-left or lower-right corner cell of a block will destroy any name associated with that block, as well as any for-mulas that refer to theCONTRACTING THE SPREADSHEET
Even though there will always be a fixed number of columns-and rows in the spreadsheet, you are free to delete them or in-sert new ones as your designs require. The commands Edit-Insert (shortcut Ctrl-I) and Edit-Delete offer you two choices:
Rows and Columns.
When you insert one or more rows, all rows at and below the insertion are pushed down. When you insert one or more columns, all columns at and to the right of the insertion are pushed to the right. You can never lose data "off the edge of the Earth" due to an insertion. For example, if there is data in row 8192 (an unlikely possibility) and you try to insert a row into the spreadsheet, your command will be thwarted and you will see the error message
Out of spreadsheet boundary
The same would be true if there were data in column IV and you tried to insert a column.
When you delete one or more columns (or rows) by using the Edit-Delete command, the columns to the right (or the rows below) are all moved to the left (or up) to fill in the gap.
When you invoke either the Edit-Insert or Delete command, you are prompted to specify the block in which you want to in-sert or delete rows or columns. You must enter a valid Quattro Pro address for these commands, although it is only the row numbers in the address that are important for the Insert or Delete Rows command. For example, to insert three rows at row 2, you could respond with the block address A2 .. A4, D2 .. D4, or L2 .. AB4. When you insert or delete columns, be sure to