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…
Looking for solution of this Assignment?
WHY CHOOSE US?
We deliver quality original papers |
Our experts write quality original papers using academic databases.We dont use AI in our work. We refund your money if AI is detected |
Free revisions |
We offer our clients multiple free revisions just to ensure you get what you want. |
Discounted prices |
All our prices are discounted which makes it affordable to you. Use code FIRST15 to get your discount |
100% originality |
We deliver papers that are written from scratch to deliver 100% originality. Our papers are free from plagiarism and NO similarity.We have ZERO TOLERANCE TO USE OF AI |
On-time delivery |
We will deliver your paper on time even on short notice or short deadline, overnight essay or even an urgent essay |