Problems

1. You are entering nonnegative whole numbers into the cell range C1:C20. Enter a data validation setting that ensures that each entry is a nonnegative whole number.

2. You are entering in the cell range C1:C15 the dates of transactions that occured during July 2004. Enter a data validation setting that ensures that each date entered occurs in July 2004.

3. With the List option in the group of data validation settings, you can generate an error message if a value that is not included in a list is entered in the cell range you’re validating. Suppose you’re entering employee first names in the cell range A1:A10. The only employees of the company are Jen, Greg, Vivian, Jon, and John. Use the List option to ensure that no one misspells a first name.

4. With the Text Length option in the group of data validation settings, you can generate an error message when the number of characters in a cell does not match the number you define. Use the Text Length option to ensure that each cell in the range C1:C10 will contain, at most, five characters (including blanks).

5. You are entering employee names in the cell range A1:A10. Use data validation to ensure that no employee’s name is entered more than twice. (Hint: Use the Custom setting and the COUNTIF function.)

6. You are entering product ID codes in the cell range A1:A15. Product ID codes must always end with the characters xyz. Use data validation to ensure that each product ID code entered ends with xyz. (Hint: Use the Custom setting and the RIGHT function.)

7. Suppose you want every entry in the cell range B2:B15 to contain text and not a numerical value. Use data validation to ensure that entering a numerical value will return an error. (Hint: Use the ISTEXT function.)

8. Set up a data validation procedure that will ensure that all numbers typed in column E will contain exactly two decimal places. Hint: Use the LEN and FIND functions.

9. The file Latitude.xlsx contains a formula to compute distance between two cities from their latitude and longitude. The file also contains the latitude and longitude of various US cities. Set up a drop-down list so that when you select a city in cell P2 and another city in cell Q2, the distance between the cities is computed in Q10.

10. Ensure that if new cities are added to the list of cities in Problem 9, the drop-down list will include the new cities.

11. The file Candybardata.xlsx contains a list of stores where we sell candy bars. The worksheet also contains the types of candy bars we sell at each store and the price charged for each candy bar. Set up your worksheet so that users can enter or select both store and candy bar from a drop-down list, and then your price shows up in D19.

    • Enable users to select a store from the drop-down box in B19.

    • Set up a drop-down list in C13 to let users choose a type of candy from a list containing only those candies sold at a selected store. (Hint: Use the INDIRECT function when defining the list.)

    • If we change the store in B19, then temporarily C19 may not list a candy sold at the newly selected store. Ensure that in C19 your worksheet says Make selection above if this is the case. For example, if B19 says CVS and C13 says gumballs then C19 should say Make selection above.

12. We have a $100,000 expense budget. In column A, we will enter expenses as the year incurred. Set up a data validation criterion that ensures that the total expenses listed in column E do not exceed our budget.

13. Set up a data validation criterion that ensures that a column of numbers is entered in descending order.