Case Problem 1Data File needed for this Case Problem: NP_EX_12-3.xlsmInvent Software David Wright is a hiring manager fo
Posted: Fri Jul 08, 2022 6:16 am
Case Problem 1Data File needed for this Case Problem:NP_EX_12-3.xlsmInvent Software David Wright is a hiring manager forthe Human Resources (HR) department at Invent Software, a growingcompany that creates software for inventory management. David isusing Excel to track the hiring process from initial job postingsthrough interviews to job offers and wants your help in developingan application to automate the process reporting key hiringmetrics. The application will be used by staffers in the HRdepartment, so it must be easy to use with safeguards to helpprevent user error. David already created part of the applicationand needs you to finish it. Complete the following:
1. Open the NP_EX_12-3.xlsm workbook located in the Excel12 >Case1 folder included with your Data Files. Save the workbook asNP_EX_12_Invent in the location specified by your instructor.
2. In the Documentation worksheet, enter your name and thedate.
3. In the Dashboard worksheet, create the following WordArtobject:a. Insert the WordArt object showing text in a black fontwith a hard red drop shadow located in the third row and secondcolumn of the WordArt gallery.b. Change the default text to InventSoftware.c. Move the WordArt object to the upper-left corner of theworksheet.d. Apply an 18-point golden glow text effect to theWordArt object, choosing the effect in the fourth row and firstcolumn of the gallery of glow effects.
4. Insert a funnel chart of the data in the range F8:G13. Moveand resize the chart to cover the range J6:K15. Change the charttitle to Application History.
5. In cell F19, create a list validation based on the data inthe range O8:O18. In cell G19, create a list validation based onthe data in the range Q8:Q13. The application will now be able toretrieve data on applicants that match specified searchcriteria
. 6. Use the arrow buttons in cells F19 and G19 to select thevalues Accountant and Onsite Interview.
7. In the Dashboard worksheet, hide the contents of columns Othrough Q.
8. Hide the Applicants, Application PivotTable, and Terms andDefinitions worksheets
. 9. Protect the workbook. Do not specify a password for thedocument.
10. In the Dashboard worksheet, unlock cells F19 and G19, andthen protect the worksheet to allow only selecting locked andunlocked cells. Do not specify a password for the protectedsheet.
11. Save the workbook, and then use the macro recorder to createa macro for this workbook named Lookup_Applicants with thedescription Retrieve application data using an advanced filter.
12. Start the recorder, and perform the following tasks tocreate the macro:a. Unprotect the worksheet.b. Click the Data tab,and then in the Sort & Filter group, click the Advancedbutton.c. In the Advanced Filter dialog box, click the Copy toanother location option button, enter Applicants[#All] in the Listrange box to retrieve data from the Applicants table, enter$F$18:$G$19 in the Criteria range box, enter $F$21:$M$21 in theCopy 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 therange H17:H19 to run the Lookup_Applicants macro. Change thelabel of the button to Retrieve Records. Protect the worksheet again, allowing users to only select locked andunlocked cells. Do not specify a password.
14. Use VBA to modify the code so the application displays amessage indicating the number of records found using the advancedfilter. In the Visual Basic for Applications editor, directlybefore the End Sub statement in the Lookup_Applicants subprocedure, 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 funnelchart of the application history for the Website Designerposition.
17. Select Website Designer and Onsite Interview from cells F19and G19, and then click the Retrieve Records button to retrieve the24 records of applicants who got only as far as the onsiteinterview stage. 18. Save the workbook, and then close it.
1. Open the NP_EX_12-3.xlsm workbook located in the Excel12 >Case1 folder included with your Data Files. Save the workbook asNP_EX_12_Invent in the location specified by your instructor.
2. In the Documentation worksheet, enter your name and thedate.
3. In the Dashboard worksheet, create the following WordArtobject:a. Insert the WordArt object showing text in a black fontwith a hard red drop shadow located in the third row and secondcolumn of the WordArt gallery.b. Change the default text to InventSoftware.c. Move the WordArt object to the upper-left corner of theworksheet.d. Apply an 18-point golden glow text effect to theWordArt object, choosing the effect in the fourth row and firstcolumn of the gallery of glow effects.
4. Insert a funnel chart of the data in the range F8:G13. Moveand resize the chart to cover the range J6:K15. Change the charttitle to Application History.
5. In cell F19, create a list validation based on the data inthe range O8:O18. In cell G19, create a list validation based onthe data in the range Q8:Q13. The application will now be able toretrieve data on applicants that match specified searchcriteria
. 6. Use the arrow buttons in cells F19 and G19 to select thevalues Accountant and Onsite Interview.
7. In the Dashboard worksheet, hide the contents of columns Othrough Q.
8. Hide the Applicants, Application PivotTable, and Terms andDefinitions worksheets
. 9. Protect the workbook. Do not specify a password for thedocument.
10. In the Dashboard worksheet, unlock cells F19 and G19, andthen protect the worksheet to allow only selecting locked andunlocked cells. Do not specify a password for the protectedsheet.
11. Save the workbook, and then use the macro recorder to createa macro for this workbook named Lookup_Applicants with thedescription Retrieve application data using an advanced filter.
12. Start the recorder, and perform the following tasks tocreate the macro:a. Unprotect the worksheet.b. Click the Data tab,and then in the Sort & Filter group, click the Advancedbutton.c. In the Advanced Filter dialog box, click the Copy toanother location option button, enter Applicants[#All] in the Listrange box to retrieve data from the Applicants table, enter$F$18:$G$19 in the Criteria range box, enter $F$21:$M$21 in theCopy 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 therange H17:H19 to run the Lookup_Applicants macro. Change thelabel of the button to Retrieve Records. Protect the worksheet again, allowing users to only select locked andunlocked cells. Do not specify a password.
14. Use VBA to modify the code so the application displays amessage indicating the number of records found using the advancedfilter. In the Visual Basic for Applications editor, directlybefore the End Sub statement in the Lookup_Applicants subprocedure, 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 funnelchart of the application history for the Website Designerposition.
17. Select Website Designer and Onsite Interview from cells F19and G19, and then click the Retrieve Records button to retrieve the24 records of applicants who got only as far as the onsiteinterview stage. 18. Save the workbook, and then close it.