Excel 2010 Bible

Part IV: Using Advanced Excel Features

578

Accepting only values that don’t exceed a total

Figure 25.7 shows a simple budget worksheet, with the budget item amounts in the range B1:B6.

The planned budget is in cell E5, and the user is attempting to enter a value in cell B4 that would

cause the total (cell E6) to exceed the budget. The following data validation formula ensures that

the sum of the budget items does not exceed the budget:

=SUM($B$1:$B$6)<=$E$5

FIGURE 25.7

Using data validation to ensure that the sum of a range does not exceed a certain value.

Creating a dependent list

As I describe previously, you can use data validation to create a drop-down list in a cell (see

“Creating a Drop-Down List”). This section explains how to use a drop-down list to control the

entries that appear in a second drop-down list. In other words, the second drop-down list is

dependent upon the value selected in the first drop-down list.

Figure 25.8 shows a simple example of a dependent list created by using data validation. Cell E2

contains data validation that displays a three-item list from the range A1:C1 (Vegetables, Fruits,

and Meats). When the user chooses an item from the list, the second list (in cell F2) displays the

appropriate items.

This worksheet uses three named ranges:

l (^) Vegetables: A2:A15
l Fruits: B2:B9
l (^) Meats: C2:C5
Cell F2 contains data validation that uses this formula:
=INDIRECT($E$2)