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:

$1.$ Open the NP$\_$EX$\_12-3.$xlsm workbook located in the Excel$12>$ Case$1$ folder included with your Data Files. Save the workbook as NP$\_$EX$\_12\_$Invent in the location specified by your instructor.

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

$3.$ In the Dashboard worksheet, create the following WordArt object:

a$.$ 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.

b$.$ Change the default text to Invent Software.

c$.$ Move the WordArt object to the upper$-$left corner of the worksheet.

d$.$ 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.

$4.$ Insert a funnel chart of the data in the range F$8$:G$13.$ Move and resize the chart to cover the range J$6$:K$15.$ Change the chart title to Application History.

$5.$ In cell F$19,$ create a list validation based on the data in the range O$8$:O$18.$ In cell G$19,$ create a list validation based on the data in the range Q$8$:Q$13.$ The application will now be able to retrieve data on applicants that match specified search criteria.

$6.$ Use the arrow buttons in cells F$19$ and G$19$ to select the values Accountant and Onsite Interview.

$7.$ In the Dashboard worksheet, hide the contents of columns O through Q$.$

$8.$ Hide the Applicants, Application PivotTable, and Terms and Definitions worksheets.

$9.$ Protect the workbook. Do not specify a password for the document.

$10.$ In the Dashboard worksheet, unlock cells F$19$ and G$19,$ and then protect the worksheet to allow only selecting locked and unlocked cells. Do not specify a password for the protected sheet.

$11.$ 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.

$12.$ Start the recorder, and perform the following tasks to create the macro:

a$.$ Unprotect the worksheet.

b$.$ Click the Data tab, and then in the Sort & Filter group, click the Advanced button.

c$.$ 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.

d$.$ Protect the worksheet.

e$.$ Stop the recorder.

$13.$ Unprotect the worksheet and create a macro button in the range H$17$:H$19$ 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.

$14.$ 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"

$15.$ Close the editor and return to the workbook.

$16.$ Choose Website Designer from the slicer to display a funnel chart of the application history for the Website Designer position.

$17.$ Select Website Designer and Onsite Interview from cells F$19$ and G$19,$ and then click the Retrieve Records button to retrieve the $24$ records of applicants who got only as far as the onsite interview stage.