Additional Case 3 Instructions Objectives • Import data from an Excel table Use exact and approximate matches to a vertical lookup table Use the IFERROR function to hide Excel error values Apply data validation to cell values Apply data validation to text lengths Calculate the next working date after a given number of days Record a macro and assign it to a macro button Unlock worksheet cells and protect the worksheet Save a workbook as a macro-enabled template The Sauce Shoppe Case Helen Jankowski works at the Sauce Shoppe, an online store based in Rock Hill, South Carolina, that sells a wide variety of hot sauces and dips. She wants to develop a workbook to process online orders. The workbook will calculate the shipping costs of the order and use that information to calculate the overall cost of the order including sales tax. Helen has stored a list of Sauce Shoppe products in an Access database table. You’ll import the database table as part of the order form and set the properties of the data connection. Complete the following:

1. Open the Sauce workbook located in the AddCases ▶ Case3 folder included with your Data Files, and then save the workbook as Sauce Shoppe as a macro-enabled workbook in the location specified by your instructor.

2. In the Documentation worksheet, enter your name and the date.

3. In the Product List worksheet, import the Product List table from the Sauce Shoppe Products database located in the AddCases ▶ Case3 folder into cell A4 of the worksheet.

4. Add the description Product list imported from the Product List table in the Sauce Shoppe Products database to the data connection for the product data. Have Excel refresh this data whenever the workbook is reopened.

5. Name the Excel table containing the product data Product_List.

6. Change the table style to Table Style Medium 6.

7. In the Order Form worksheet, in cell B26, enter a formula that returns the current date.

8. In cell B27, add a data validation to limit the possible type of delivery values in the cell to Standard, 3 Day, 2 Day, or Overnight based on the values in the range A14:A17 of the Delivery Calculator worksheet.

9. In cell B28, calculate the number of workdays to deliver the order based on the lookup table in the range A14:C17 of the Deliver Calculator worksheet and using the value of cell B27 as the lookup value. If the formula returns an error value, display no text in the cell.

10. In cell B29, calculate the estimated delivery day, assuming that deliveries are only made on the weekdays, using the value in cell B28 to estimate the number of workdays that have passed from the current date in cell B26. Have the estimated delivery date skip holidays using the list of holiday dates in the range A20:A75 of the Delivery Calculator worksheet. If the formula returns an error value, display no text in the cell.

11. In the range A32:A41, add data validation to limit the product ID of items ordered by the customer to the list of product IDs in the range A5:A70 of the Product List worksheet.

12. In the range B32:C41, use the Product_List table as a lookup table to retrieve the name of the product ordered by the customer. Use the corresponding Product ID in column A as the lookup value. If the formula returns an error value, display no text in the cell. 13. In the range D32:D41, use the Product_List table as a lookup table to retrieve the price of the product ordered by the customer. Use the corresponding value in column A as the lookup value. If no Product ID is entered in the corresponding cell in column A, display no text in the cell. 14. In the range F32:F41, calculate the charge for the item ordered by multiplying the price of the item by the quantity ordered. If the formula returns an error value, display no text in the cell. 15. In cell F43, calculate the subtotal of the charges for the items ordered by the customer. 16. In cell F44, calculate the sales tax on the order using the tax rate quoted in cell H33. 17. In cell F45, calculate the total cost of shipping and handling based on the following rules: o Calculate the initial shipping charge by looking up the shipping fee in the table in the range A6:B11 of the Delivery Calculator worksheet using cell F43 as the lookup value. o Any merchandise subtotal greater than $100 receives free shipping. o Add a surcharge based on the type of delivery by looking up the surcharge from the range A14:C17 on the Delivery Calculator worksheet and the type of delivery specified in cell B27. o If the formula returns an error value, display no text in the cell. 18. In cell F47, calculate the total bill by adding the subtotal, sales tax, and shipping & handling fee. If the formula returns an error value, display no text in the cell. 19. Use data validation to limit the value in cell E26 to either VISA or MasterCard, which are the credit cards that The Sauce Shoppe accepts. 20. VISA and MasterCard credit card numbers are 16 digits. Add data validation to limit the text in cell E27 to 16 characters. Apply the Number format with no decimal places to the value in the cell. 21. Cell H27 is used for storing a three-digit security code. Add data validation to set the length of the text in the cell to three characters. 22. In cell E28, add data validation to verify that the expiration date entered in the cell is past the date specified in cell B26 (the date of the order). 23. Record a macro named Copy_Address that performs the following steps: o Select and copy the range B8:B13. o Paste the copied cells into the range B19:B24. o Select cell B26. 24. Assign the Copy_Address macro to the macro button, “Copy Shipping Address —>” 25. Unlock all of the cells in the worksheet except those containing formulas (indicated by the light green fill). 26. Protect the Order Form worksheet from being changed. Do not specify a password for the sheet protection. 27. Enter the customer information listed below. Use the macro button to copy the shipping address into the billing address. Customer: Jaime Kingsolver Order No.: OR1284 Address 1: 414 Jefferson Lane City: Newberry State: South Carolina ZIP: 29108 Phone: (803) 555-1021 28. Choose Standard shipping for the order. 29. Add the following items to the order and calculate the total bill: 3 of item FM008, 1 of item EX012, and 1 of item HVM015. 30. Enter the following VISA credit card number for the customer: 4123456789012340 with the security code 999. Enter 3/1/2016 as the expiration date and Jaime Kingsolver as the cardholder’s name. 31. Print the order form on a single sheet of paper in portrait orientation. 32. Save the workbook. 33. Delete all of the customer- and order-specific information from the Order Form worksheet. Save the file as Order Form in Excel Macro-Enabled Template format in the location specified by your instructor. Do not have Excel clear the external data before saving the template