# Excel assignment | Computer Science homework help

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.