5. In cell B18, create a formula using nested IF functions and VLOOKUP functions to determine what to do with the returns. Use the lookup table in the Return Data worksheet. Refer to Figure 8-46 for some hints on how to create the formula. Figure 8-46 Formula for return resolution The The col If the value lookup The table_ index num The range in B16 is: value is: array is: is: lookup is: Nested IF Function Destroy B14 Dept_Returns 2 FALSE IF(B16-"Destroy"VLOOKUP(B14, Dept_Returns,2,FALSE), Return to B14 Dept_Returns 3 FALSE IF(B16="Return to Mig", VLOOKUP Mfg (B14,Dept_Returns,3,FALSE]. Repack B14 Dept_Returns 4 FALSE IF(B16-"Repack".VLOOKUP(B14, Dept_Returns, 4, FALSE), Restock B14 Dept_Returns 5 FALSE IF(B16="Restock",VLOOKUP(B14, Dept_Returns,5,FALSE), "None"Will 6. Test your routing slip by entering the following information: . Product Name: Deluxe Vacuum . Department: Electronics . Resolution: Return to Mig . Customer Assistant: 01265 7. Protect all cells in the Return Routing worksheet except those in which you enter data. 8. In the Return Analysis worksheet, complete the following analysis of the Return Data worksheet using COUNTIF: a. Compute the total Returns by Department. b. Compute the total Returns by Resolution. 9. Save the workbook, and then close it.Case Problem 4 CREATE Data File needed for this Case Problem: Athey.xIsx Athey Department Store Athey Department Store in Fort Dodge, lowa, has always accepted returns of any product purchased in its store. Mitchell Athey wants to develop a system for handling the returns. A routing slip will allow him to monitor who handled the return at each step. He will also be able to collect the returns data in a worksheet for count of returns in each category. Mitchell has started developing the routing slip, and he wants you to finish creating it. Complete the following: 1. Open the Athey workbook located in the Excels > Case4 folder included with your Data Files, and then save the workbook as Athey Routing Slip in the location specified by your instructor. 2. In the Documentation worksheet, enter your name and the date. 3. Create a defined name for the table on the Return Data worksheet to help you when you create formulas with VLOOKUP functions. (Hint: Use a name other than Return Tbl because this name is already used as the defined name for the table on the Return Data worksheet.) 4. In the Return Routing worksheet, do the following: a. In cell B9, use a date function to display the current date. b. In cell B11, use an input message to inform the user to enter a product description in this cell. C. In cell B14, use a list validation for the Department located on the Return Data worksheet. d. In cell B16, use a list validation for the Resolution located on the Return Table worksheet (the range B1:[1). e. In cell B20, use an input message to inform the person entering the information to enter their name, initials, or Employee ID in this area
