You are working in ABBC Auto$$s Sales Department as an analyst. The sales manager, Leroy Mason, wants to automate the worksheet he uses to calculate monthly sales commissions so that others can use it$.$ ABBC Auto pays each salesperson a base salary plus a commission. This commission value has changed so this value for all new entries should be $7\%$ of the sales amount. Leroy asks for your help in ensuring users enter valid data and do not change or delete the sales data the workbook already contains. He also wants to include a chart comparing the total sales of each salesperson without revealing their names.

Complete the following:

$1.$ Open the ABBCCarSales.xlsx workbook and save the file as the macro$-$enabled workbook ABBCCarJulySales$($yourlastname$).$xlsm$.$

$2.$ On the SalesRepSummary worksheet, summarize each salesperson$$s total sales and total commissions based on the data listed on the SalesList worksheet. Make sure that the table will auto$-$update when new data is added to the SalesList through row $250.$

$3.$ Examine the SalesEntryForm worksheet and correct its formula error. Create validation rules so that unique sales rep names appear in a drop$-$down list using the data on the SalesRepSummary worksheet, sales dates are limited to valid dates in $2022,$ and sales amounts must be positive values. Annotate the validation rules so users entering the data will know what data to enter and what is allowed.

$4.$ Create a macro named UpdateSalesList with shortcut key Ctrl$+$Shift$+$C$,$ assigned to this workbook. The purpose of the macro is to automate the moving of the data entered in the sales entry form to the SalesList worksheet and then to clear the data in the sales entry form. Create a custom button on the SalesEntryForm worksheet with the label Record Sale that automatically launches this macro. For best results, use the following procedure:

a$.$ Before recording the macro, assign range names to the following cells:

SalesEntryForm!B$5$ Sales$\_$Rep$\_$Name

SalesEntryForm!B$6$ Sales$\_$Date

SalesEntryForm!B$7$ Sales$\_$Amount

SalesEntryForm!B$8$ Commission

SalesList!A$5$ Top

b$.$ Click the Use Relative References button in the code section of the Developer tab to make sure that when recording your macro, the insertion point is relative to the active cell. Be sure the button is highlighted in dark gray to indicate that the feature is on before you begin recording the macro.

c$.$ Enter some sample data into the sales entry form

d$.$ Click Record Macro, then assign the appropriate name and shortcut key. Store the macro in this workbook and enter a brief description

e$.$ Open the Go To box by pressing the F$5$ function key, then select Top in the list of range names to move the starting point on the SalesList worksheet to cell A$5.$

f$.$ Press Ctrl$+$ to move the active cell to the bottom of the list. Then, using the arrow keys, move the active cell down one row to the first empty cell. Type $=$Sales$\_$Rep$\_$Name. Move to the next column where the user will enter the sales date, and then type $=$Sales$\_$Date. Repeat this procedure to enter the sales amount and commission using their corresponding range names.

g$.$ Copy the cells with the formulas you just entered, and then use the Paste Special $$ Values option to paste the cells in the same location. This paste action removes the formulas but keeps the values that appear in the data entry form.

h$.$ Go to the Sales$\_$Rep$\_$Name range and delete the entry. Move down one cell and delete the sales date and sales amount. You must clear the input data to leave the cells ready for the next user to enter new data. Press the Esc key.

i$.$ Stop recording the macro.

j$.$ Create a custom macro button called Record Sale Info on the SalesEntryForm worksheet that automatically launches this macro.

k$.$ Remove the sample data from the SalesList worksheet that you used while recording the macro.

NOTE: It is common for this macro to crash with an error. If that happens, the easiest thing is to delete your macro and re$-$record it$.$ Make sure that you are following the directions above closely, such as using the keyboard specifically when specified instead of your mouse.