Homework answers / question archive / Illustrated Excel 2019 | Module 9: End of Module Project 1 Bachman Group AUTOMATE WORKBOOK DATA GETTING STARTED Open the file IL_EX19_EOM9-1_FirstLastName_1

Illustrated Excel 2019 | Module 9: End of Module Project 1 Bachman Group AUTOMATE WORKBOOK DATA GETTING STARTED Open the file IL_EX19_EOM9-1_FirstLastName_1

MS Excel

Share With

Illustrated Excel 2019 | Module 9: End of Module Project 1

Bachman Group

AUTOMATE WORKBOOK DATA

GETTING STARTED

• Open the file IL_EX19_EOM9-1_FirstLastName_1.xlsm, available for download from the SAM website.
• Save the file as IL_EX19_EOM9-1_FirstLastName_2.xlsm by changing the “1” to a “2”.
  • If you do not see the .xlsm file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file IL_EX19_EOM9-1_FirstLastName_2.xlsm still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

• PROJECT STEPS

1. Van Nguyen is the senior branch manager for the Bachman Group, a consulting firm with headquarters in Stamford, Connecticut. He is using an Excel workbook to track the revenue generated by the 12 branch offices in the Midwest and determine whether they are meeting the company's quarterly revenue goal. He asks for your help in automating the workbook.
   
   Go to the Midwest Branches worksheet, which lists the revenue generated by the Midwestern branches in Quarter 1. Add a title to the worksheet by running the Title macro.
2. Van also wants to identify the lowest two revenue values per quarter and wants to use the Lowest button to identify them.
   Record a new macro stored in This Workbook using Lowest as the macro name. With the macro recording, select the range F4:F15. Apply the Bottom 10 Items conditional formatting rule to the selected range, using 2 as the number of bottom values to highlight. Use the default fill and text color for the highlighting, select cell G1, and then stop recording the macro. Clear the conditional formatting rules in the range F4:F15. Assign the Lowest macro to the Lowest button so that Van can identify the bottom two revenue values by clicking the button. Run the Lowest macro.
3. Van created a macro named Highest and assigned it to the Highest button. The macro highlights the top 10 amounts of revenue generated in a quarter, but he wants to highlight only the top two amounts.
   Open the Highest macro in the Visual Basic Editor. Change the .Rank = 10 line of code to .Rank = 2 so that the highlighting rule identifies the top two values. Edit the comment on Line 4 (after the "Highest Macro" comment) to use Highlight top 2 values as the comment text. Save your work and close the Visual Basic Editor. Run the Highest macro.
4. Van created a macro named CommandButton to indicate whether the total quarterly revenue met the goal displayed in cell I3. He wants to use a button to run the macro. Add a Button (Form control) to cell H2. Edit the text to display Goal on the button. Resize the button to a height of 0.3" and a width of 1.2".
5. Assign the CommandButton macro to the Goal button, and then use the Goal button to run the CommandButton macro.
6. Van also wants to add a check box in cell C20 that displays "Mai Chen", the name of the Bachman Group manager in Stamford who reviews the quarterly revenue totals. If the check box is selected, the Approved by text in cell K3 should display "Stamford Office".
   Add a Check Box (Form Control) in cell C20. Use Mai Chen as the check box label. Format the check box to link to cell $L$3. Align the top of the Mai Chen check box with the top of the Ross Doren check box. (MAC Hint: Resize the checkbox so it remains in cell C20.)
7. Assign a new macro to the Mai Chen check box by opening the Visual Basic window for the check box and entering the code shown in Figure 1. (Hint: The CheckBox2 text in Figure 1 might appear with a different number in your code.) When you finish, save the code, remove the checkmark from the Mai Chen check box (if necessary), and then click the Mai Chen check box to test the code.

8. Van created a macro named SendTo to enter the name of the correct city in cell K5 depending on the text in cell K3. If cell K3 contains "Stamford Office", cell K5 should display "Stamford". Edit the SendTo macro to change ActiveCell.Formula = "Blank" to ActiveCell.Formula = "Stamford" in the If statement. Run the SendTo macro to enter the correct text in cell K5.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.