Apply-It #3 Advanced Modules Download and open the Apply-It 3 Start File

Apply-It #3 Advanced Modules

Download and open the Apply-It 3 Start File from Canvas.

Save the workbook as FirstName.Lastname.Apply-It 3 as a Macro-enabled workbook or you will lose all the Macros you are about to create.

Part 1: Macros, Validation Rules, Conditional Formatting,
XLOOKUP, Protecting Worksheets

Roberta Olson is a dispatch manager for Milwaukee Cheese, a large cheese and dairy supply company operating in them Midwest. One of Roberta’s jobs is to provide the shipping assignments to the company drivers among 27 Wisconsin cities. It is company policy that no driver logs more than 350 miles in a single day driving from one distribution center to the next. You will help Roberta develop an Excel application for entering driving assignments that fulfill company policy.

Roberta’s worksheet includes a Driving Form worksheet in which the user will enter commands to store each leg of a driving itinerary. The legs will then be stored in the Itinerary table of the Driving Itinerary worksheet. The distances between the 27 cities are stored in the Distance Table worksheet.

In this application, you will use validation tests to ensure that a driving leg is one of the 27 cities, and the total driving distance does not exceed 350 miles. You will create a macro to add driving legs from the Driving Form to the Driving Itinerary.

In the Driving Form worksheet, the user will enter individual legs of a driving itinerary. Notice that we already have 2 legs of our itinerary, listed in the Driving Itinerary worksheet. We will begin our 3rd leg in Eau Claire and end in Beloit, which is 40 miles as shown in the Distance Table worksheet.

Review the Driving Itinerary worksheet and see that you have already logged 2 legs of the driver’s itinerary, ending the 2nd leg in the city of Eau Claire.

In cell C6 of the Driving Form worksheet, type the next leg number, 3. In cell D6 type the Starting City for leg 3, Eau Claire. Type the Ending City, Beloit, in cell E6.

Create a formula in cell F6 to calculate the mileage between starting and ending cities using information in the Distance Table worksheet. Test your formula. Does it calculate the correct distance between Eau Claire and another city? What if the starting city is a city other than Eau Claire? Hint: XLOOKUP

Create a formula in cell C6, replacing the number 3, which calculates the next leg needed to be added to the legs in column B of the Driving Itinerary worksheet. As the user adds legs, the number should increment by 1. Hint: MAX formula

The Driving Entry Form will use the AutoComplete feature to fill in the city names in cells D6 and E6. There is no need to display the city names in rows 7 through 33 for AutoComplete to work. Hide rows 7 through 33 in the worksheet so that content doesn’t distract the user.

For the range D6:E6, create a custom validation rule to create a formula ensuring the driving leg is valid. For a driving leg to be valid both of these rules must be true:

Start and end in one of the 27 cities. City names are listed in the cities named range. Hint: use COUNTIF to check if the starting|ending cities in D6/E6 match your named range. Section EX 12-7e, Insight.

The total driving distance, stored in the dist named range, should not exceed 350 miles.

If the validation rule is violated, display a warning box with the title Invalid Data and the message You either mistyped the city name or adding this leg will result in a total driving distance exceeding 350 miles.

Create a macro for this workbook named Add_Leg with the description Add a leg to the driving itinerary. Begin your macro on the Driving Form sheet. Macros record every keystroke so be sure these directions are accessible without clicking away from Excel during the macro recording. The macro, set up with Ctrl + G (upper case G) as your shortcut, should perform the following tasks:

Go to the Driving Itinerary worksheet to give the sheet the focus.

Go to the travel_end cell using the named range

Insert a new sheet row directly above the travel_end cell on the Driving Itinerary worksheet. Hint: Insert the row in such a way that only one cell is active after the insert row is complete. Do not insert the row by selecting the row number to select the entire row.

Go to the Driving Form worksheet and copy the values in the range C6:F6 to the clipboard.

Return to the Driving Itinerary worksheet and paste the values and number formats into the active cell of the worksheet.

Go to the Driving Form worksheet and prepare the entry sheet for the next leg:

Copy the value from the Ending City, cell E6, into the Starting City, cell D6.

Delete the contents of the Ending City, cell E6.

End the macro

Your Driving Itinerary should now have 3 legs, ending with the city of Beloit. Insert a macro button in the range C35:D36 on the Driving Form worksheet to play the Add_Leg macro. Change the label of the macro button to Add Leg to Itinerary.

Use the macro and the data form to enter the following legs into the driving itinerary:

Beloit to Madison
Madison to La Crosse

Verify that when you try to enter a new driving leg of La Crosse to Marshfield, the application warns you that you are about to exceed the allowed driving distance. Do not enter Marshfield as the last leg of the trip.

When a macro switches between worksheets, the quick jump from one sheet to another can be distracting. To correct this problem, edit the Add_Leg sub procedure in the Visual Basic for Applications editor.

Directly after the initial comment section at the top of the sub procedure insert the following command to turn off screen updating while the macro is running:
Application.ScreenUpdating = False

Directly before the closing End Sub command at the bottom of the sub procedure, insert the following command to turn screen updating back on:

Application.ScreenUpdating = True

Insert a macro button next to your Add Leg to Itinerary button

The label of this button should be Miles Used

When pressed, a message box should pop up saying You have used XXX miles out of 350, where XXX is the distance in the named range dist.
Hint: Create a new subroutine using the function MsgBox (prompt, [ buttons, ] [ title, ] ). Here is a tutorial on the VBA MsgBox function:
https://trumpexcel.com/vba-msgbox

For the prompt argument, use the & to combine text and the variable Range(“dist”) to display “You have used XXX miles out of 350”.

Use the buttons argument to display the i information icon.

Use Miles as the title of the message box.

Name the macro Miles.

In the Driving itinerary worksheet, create a macro named PrintOrder. Assign the shortcut key Ctrl+D ((upper case D). The macro should:

Set the Order Form print area to the range B1:E20

Center the print area horizontally.

Add a footer in the right-hand side of the page showing the current date and time (the date/time stamp will be updated every time you run the macro).

Notice that there is a comment in cell B2. Ensure the comment does not print when you run your macro.

The macro should only set the print variables, not actually send output to a printer.

Insert the printer icon or use a similar image (but not a form control button) and assign the PrintOrder macro to the image.

You tried to set up protection so that no one would accidentally change the cities and miles on the Distance Table worksheet in cells B4:AC31. However, someone was able to change some cells so the worksheet protection setup isn’t quite right. Hint: check out this video if you are stuck Highlight Locked or Unlocked Cells in Excel using Conditional Formatting

Unprotect the worksheet.

Create a conditional format rule to highlight all the unlocked cells, starting in cell B4 and ending in cell AC31. Hint: use the CELL function with the “protect” argument & relative reference when creating your formula.

Fix the cells that are set up incorrectly so they will be locked when you protect the sheet. Do not remove the conditional formatting.

Protect the worksheet with NO password.

Save the workbook as a macro-enabled workbook

Part 2: Grade Calculator with Power Query & Functions

Have you ever tried to copy data into Excel, but when you did you got one unwieldly Column that seemed impossible to turn into a table? Getting raw data that is in a format unsuitable for analysis is typical, and it’s important to learn skills to ‘clean’ the data for your needs.

In this assignment you will create a grade calculator using PowerQuery and formulas by transforming downloaded data from a grading system. The calculator will show each assignment completed during the semester and will calculate the student’s current grade. Your goal is to calculate the student’s % Grade

Open the StudentRawData worksheet. This is downloaded data from a student’s gradebook. For this fictitious class:

All Module eBook Activities and Projects except Modules 1 and 2 are required (20 assignments). Modules 1 and 2 eBook Activities are extra credit.

Students will drop the 2 lowest grades from the 24 SAM Module assignments. Students can choose to skip an assignment, resulting in a score of zero.

Extra credit of 5 points will be given for any extra credit assignment with a score of 4 or more. Scores below 4 are worth zero points.

Apply-IT assignments are worth 250 points.

It is possible to score > 100% in this class because of the extra credit.

We need to clean up the data on the StudentRawData worksheet. Load the data from column A into Power Query using Get & Transform Data. Check the box in the Create Table wizard that says My table has headers.

Remove the first 3 rows using the Remove Top Rows command.

Use the Remove Alternate Rows command to keep every 2 rows, starting with the 2nd row.

Your query should look like this

Hint: if you removed the wrong rows, delete your step as shown below and try again.

Load your data to a new worksheet by creating a table.

Name the table MyGradesTbl.

Rename the worksheet Grade Calculator. Points will be deducted if you miss this step.

As you work on this problem, you may want to freeze your top row so when you scroll you can see the Column labels.

We need to move all the grades to Column B in the correct row but copying them one-by-one is not efficient. Let’s have Excel do the work for us.

Type Assignment in cell A1. Type Grade in cell B1.

Let’s copy only the grades to Column B. Notice that all of the grades are in odd rows. How can we ask Excel to check if the row number is odd, & if so, copy the assignment value from Column A into Column B? Create a formula using the IF statement to return the current row’s assignment value from Column A if the row is ODD. Return a blank in the cell if the row is not ODD. Use the functions IF, ISODD & ROW to solve this puzzle.

ISODD function returns TRUE if the argument number is odd, or FALSE if the number is even. Example: =ISODD(1) returns TRUE

ROW() function returns the row number of the reference.
Example: =ROW(A1) returns the number 1

Hint: start your formula in B2 with =IF(ISODD(

We need values in Column B, not formulas. Remove the formulas by using Copy / Paste Values to remove the formulas. Remove any extra Columns you may have used to accomplish this step, so that you only have Column A and Column B in your table. Copying Formats with Paste Special Ex 2-11c. Hint: Copy and paste special the entire table or sheet. Excel can be finicky about allowing paste special on part of a formatted table (for consistency).

Move the grades in Column B up one row.

Delete the rows where the cells in Column B are blank. Don’t delete one row at a time; imagine instead of 30+ rows you have thousands of rows. Harness your Excel knowledge to complete this task. You should have 35 rows at the end of this step. Hint: filter to show only the rows you want to delete

Use Flash Fill to create Columns C My Score and D Points Available using the data in Column B.

The number before the / is the score on the assignment, such as a score of 3 on Module 2 Exam. Any item that the student did not complete will have a dash in front of the / symbol. (ie: -/5)

The number after the / is the Points Available (ie: -/5)

This student decided not to complete the Modules 6 eBook Activity and received a zero. Future assignments that have not been completed are indicated by a dash in the My Score column.

Use a formula to calculate the total points from assignments completed excluding extra credit to be used in the denominator for the student’s % Grade

We need to figure out which cells are extra credit and which assignments the student has not completed, and therefore will not be used for the denominator of the percent grade. In cell E1, type Add to Denominator. In Column E, use a formula to indicate how many points for graded assignments should count toward the grade bases, leaving blank any assignment that the student didn’t complete or is extra credit.

There are many ways to solve this problem. Here is one way: If both of the statements below are true, return the points available from the current row, otherwise insert a blank into the cell. Exploring Logical Functions EX7-5

If the Points Available are more than 5, the assignment is required and should be counted in the denominator.

If My Score is less than or equal to 250, the student has completed the assignment and the assignment should be counted in the denominator.

Use a formula to determine if the student earned Extra Credit. Extra credit of 5 points will be given for any extra credit assignment with a score of 4 or more. Scores below 4 are worth zero points. Put the student’s results in Column F and label the Column Extra Credit. Exploring Logical Functions EX7-5

We need a column showing just SAM assignments the student has either completed or decided to receive a zero. This column will be used to calculate which 2 SAM assignments to drop.

Type SAM Assignments in cell G1.

SAM assignments are all worth 100 points, so create a formula to copy any assignment’s My Score where Points Available = 100. Return a dash if the criteria are not met.

Add a Total Row to your table using the Table Design option checkbox. Display the SUM for columns Add to Denominator, Extra Credit and SAM Assignments. Your totals will be in row 36. There is no need to reduce your SAM assignment totals by your 2 lowest scores since this will be taken care of in the next step.

Create a formula C36 to calculate the total points the student has earned from all assignments except extra credit and your 2 lowest SAM scores. To calculate the total My Score:

Add all of the points in the My Score Column where the Points Available are not extra credit. Hint: Calculating Conditional Sums with SUMIF EX 7-6b

In cell C36, subtract the two lowest scores from SAM Assignments listed in column G. You must use a formula to determine these values to get full credit. Hint: Google Excel’s SMALL function to learn how to use this formula

Your formula in C36 will look like this =SUMIF(XX)-SMALL(XX)-SMALL(XX)

Type Percent Grade into cell I1 and Letter Grade in cell J1. Calculate the student’s percentage grade in I2 using the Totals in row 36.

Add the My Score and Extra Credit totals in row 36.

Divide the result by your Denominator.

The denominator must be reduced by 200 points since we dropped the 2 lowest SAM scores.

In cell J2, use a formula to determine the student’s grade letter based on the table below.
Hint: you will need to build a table for your lookup_array and return_array arguments somewhere on your worksheet.

Test your grade calculator to see if your formulas are robust by creating various grades for this student. With a few small changes, the calculator you built can be used to calculate your grade in this class and other classes as well.

Finished

Congratulations, you have finished your last Apply-It assignment!

Submit to Canvas. Celebrate your accomplishments and all you have learned this semester.

Page 2