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.
Figure 22-3. Entering pop-up text to help users with entry
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
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
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.
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.
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
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.
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.