"Case Problem 1 Data File needed for this Case Problem: NP_EX_12-3.xlsm Invent Software David Wright is a hiring manager for the Human Resources (HR) department at Invent Software, a growing company that creates software for inventory management. David is using Excel to track the hiring process from initial job postings through interviews to job offers and wants your help in developing an application to automate the process reporting key hiring metrics. The application will be used by staffers in the HR department, so it must be easy to use with safeguards to help prevent user error. David already created part of the application and needs you to finish it. Complete the following:

Open the NP_EX_12-3.xlsm workbook located in the Excel12 > Case1 folder included with your Data Files. Save the workbook as NP_EX_12_Invent in the location specified by your instructor. In the Documentation worksheet, enter your name and the date. In the Dashboard worksheet, create the following WordArt object:

Insert the WordArt object showing text in a black font with a hard red drop shadow located in the third row and second column of the WordArt gallery. Change the default text to Invent Software. Move the WordArt object to the upper-left corner of the worksheet. Apply an 18-point golden glow text effect to the WordArt object, choosing the effect in the fourth row and first column of the gallery of glow effects.

Insert a funnel chart of the data in the range F8:G13. Move and resize the chart to cover the range J6:K15. Change the chart title to Application History. In cell F19, create a list validation based on the data in the range O8:O18. In cell G19, create a list validation based on the data in the range Q8:Q13. The application will now be able to retrieve data on applicants that match specified search criteria. Use the arrow buttons in cells F19 and G19 to select the values Accountant and Onsite Interview. In the Dashboard worksheet, hide the contents of columns O through Q. Hide the Applicants, Application PivotTable, and Terms and Definitions worksheets. Protect the workbook. Do not specify a password for the document. In the Dashboard worksheet, unlock cells F19 and G19, and then protect the worksheet to allow only selecting locked and unlocked cells. Do not specify a password for the protected sheet. Save the workbook, and then use the macro recorder to create a macro for this workbook named Lookup_Applicants with the description Retrieve application data using an advanced filter. Start the recorder, and perform the following tasks to create the macro:

Unprotect the worksheet. Click the Data tab, and then in the Sort & Filter group, click the Advanced button. In the Advanced Filter dialog box, click the Copy to another location option button, enter Applicants[#All] in the List range box to retrieve data from the Applicants table, enter $F$18:$G$19 in the Criteria range box, enter $F$21:$M$21 in the Copy to box, and then click OK to apply the advanced filter. Protect the worksheet. Stop the recorder.

Unprotect the worksheet and create a macro button in the range H17:H19 to run the Lookup_Applicants macro. Change the label of the button to Retrieve Records. Protect the worksheet again, allowing users to only select locked and unlocked cells. Do not specify a password. Use VBA to modify the code so the application displays a message indicating the number of records found using the advanced filter. In the Visual Basic for Applications editor, directly before the End Sub statement in the Lookup_Applicants sub procedure, add the following commands: recNum = Application.WorksheetFunction.Count(Range("K:K")) MsgBox recNum & " record(s) found" Close the editor and return to the workbook. Choose Website Designer from the slicer to display a funnel chart of the application history for the Website Designer position. Select Website Designer and Onsite Interview from cells F19 and G19, and then click the Retrieve Records button to retrieve the 24 records of applicants who got only as far as the onsite interview stage. Save the workbook, and then close it."