Controlling Valid Entries

Controlling Valid Entries

You can help guide yourself and others through data entry in Calc by restricting cells to receive specific values and ranges of whole numbers , decimal values, dates, and times. You can also specify specific text lengths allowed in cells.

For example, you can have a cell reject decimal or negative entries by setting it up to receive only whole numbers. Or you can set up a cell to reject any text entries longer than 20 characters .

When you set up validity rules for cells, you can also create help text that will pop up when any of the cells is selected, telling the user what's allowed in the cell. You can also provide warnings when invalid entries are made, and allow invalid entries to be either accepted or rejected.

  1. Select the cell(s) for which you want to set up validity rules.

    See Selecting Non-Adjacent Cells on page 535.

  2. Choose Data > Validity.

  3. Set the validity rules, help text, and error messages. Use Figure 22-2 through Figure 22-4 for guidance.

    Figure 22-2. Setting the values for valid entries

    graphics/22fig02.jpg

    Figure 22-4. Typing the text that will appear on an invalid entry

    graphics/22fig04.jpg

  4. Click OK.

Figure 22-3. Entering pop-up text to help users with entry

graphics/22fig03.jpg

Figure 22-5 shows the results of the settings in Figure 22-2 through Figure 22-4.

Figure 22-5. The valid entries feature in action

graphics/22fig05.jpg

"-->

Using Scenarios

Calc's scenarios tool is one of the coolest features any spreadsheet application could provide. In its simplest form, it lets you create a drop-down list of values to select from for a given cell . In its fullest form, it lets you enclose a set group of cells whose contents change when you select a different item from the drop-down list.

For example, you could set up a scenarios list that lets you choose among different percentage rates for a cell. That cell, in turn , is used in formulas elsewhere in the spreadsheet; so when you select a different percentage rate from the drop-down list, the values in the spreadsheet adjust automatically.

In a more involved example, you could set up a scenarios list that lets you choose among names of different home equity lenders. As you select a different lender, you get different values for things like annual percentage rate, the percentage of equity you can borrow against, and the number of loan years . The cells containing these values, in turn, can be referenced in formulas elsewhere in your spreadsheet to calculate things like the total amount of money you can borrow from that lender, the total amount of interest you'll end up paying, and how much of your credit card debt you'll be able to pay off with the loan amount.

In the latter example, each home equity lender you set up would be a single scenario. A single drop-down list contains multiple scenarios.

Figure 22-6 shows three examples of scenarios.

Figure 22-6. Examples of scenarios

graphics/22fig06.jpg

Creating Scenarios

Before you jump in and start creating scenarios, set up your spreadsheet, and try to group your scenario variables in a single area of the spreadsheet. In particular, set up row or column labels next to the values that will be used in the scenarios.

  1. Select the cells you want to include in your scenarios.

    You must select at least two cells to create a scenario. If you only have one cell you want to create a drop-down list for, just leave the second cell blank.

    You can select cells vertically and horizontally. All selected cells will be included in your scenarios. You can also select non-adjacent cells. Each non-adjacent cell will have its own drop-down list, but you can switch among scenarios from any of the non-adjacent drop-down lists to change all non-adjacent cell values.

  2. Choose Tools > Scenarios.

  3. In the Create Scenarios window, type a name for the scenario, add comments about the scenario, and mark the settings you want.

  4. Click OK.

  5. To add another scenario to the drop-down list, select all the cells in the scenarios area, choose Tools > Scenarios, create the new scenario, and click OK.

    The name of the new scenario is displayed in the scenarios list title bar. Change all appropriate cell values in the scenario area of the spreadsheet to reflect the scenario you just added.

  6. Rinse and repeat. (Sorry. Just a little clean humor.)

Scenarios are stored in individual sheets, not for the entire document. So you can have duplicate scenario names from sheet to sheet.

You can also apply conditional formatting to scenario cells to display different scenario values using different cell formats.

You cannot switch between scenarios if cell protection is turned on.

Modifying Scenarios

There are two different aspects of modifying scenarios: by modifying the values in each scenario, or by modifying the properties of each scenario (name, comments, or border color ).

Changing Scenario Values
  1. Select the sheet containing the scenario(s) you want to modify.

  2. In the scenarios drop-down list, select the scenario containing the values you want to modify.

  3. Change the scenario's values directly in the spreadsheet.

You might be tempted to apply cell protection to scenarios so they can't be easily changed. Resist that temptation , because with cell protection turned on you can't switch among scenarios.

Changing Scenario Properties

After you've created scenarios, you can rename them, modify their comments, and change the border colors of the scenarios area.

  1. Select the sheet containing the scenario(s) you want to modify.

  2. Press the F5 key to display Navigator.

  3. In the Navigator window, click the Scenarios button to display the list of scenarios.

  4. Right-click the name of the scenario you want to modify, and choose Properties.

    graphics/22inf01.jpg

  5. In the Edit Scenarios window (Figure 22-7 on page 607), change the properties for the scenario.

    Figure 22-7. Creating a scenario

    graphics/22fig07.jpg

  6. Click OK.

Deleting Scenarios

When you delete a scenario, you also delete the spreadsheet values associated with itunless the scenario you're deleting is the currently selected scenario in the spreadsheet. In that case, the values remain in the spreadsheet, but the scenario title bar says "(empty)". When you switch to another scenario, the data in the (empty) scenario is replaced by data for the newly selected scenario.

  1. Select the sheet containing the scenario(s) you want to modify.

  2. Press the F5 key to display Navigator.

  3. In the Navigator window, click the Scenarios button to display the list of scenarios.

  4. Right-click the name of the scenario you want to delete, and choose Delete.

  5. Click Yes in the confirmation window.