Apply data validation rules to cells F$15$:F$24$ on the $2019$ Shipments worksheet and cells F$15$:F$26$ on the $2020$ Shipments worksheet.

a$.$ Require whole numbers between $500$ and $5,000.$

b$.$ Do not allow blanks.

c$.$ Circle invalid data in both the $2019$ Shipments and $2020$ Shipments worksheets.

$$ Add this comment to cell F$20$ on the $2020$ Shipments worksheet:

Look up cost$.$

$5.$ Unlock the blank cells so someone can update the data after the worksheet protection has been applied. Hint: There is one cell on the $2019$ Shipments worksheet and one cell on the $2020$ Shipments worksheet. Use the data validation circles to find them.

$6.$ Apply data validation rules to cells C$15$:C$24$ on the $2019$ Shipments worksheet and cells C$15$:C$26$ on the $2020$ Shipments worksheet.

a$.q,$ Values for these cells must come from the Locations named range.

b$.$ Include an in$-$cell drop$-$down list. Do not allow blanks.

c$.$ Circle invalid data in both the $2019$ Shipments and $2020$ Shipments worksheets.

$7.$ Add a hyperlink to cell C$16$ on the $2019$ Shipments worksheets to link to cell A$26$ on the Locations worksheet. Test the link.

$8.$ Use Consolidate to create a summary of total units shipped and total cost to date.

a$.$ Place the consolidated data on the Summary worksheet in cells B$2$:C$10.$

b$.$ Use the Sum function to consolidate the data from cells B$3$:C$11$ in the $2019$ and $2020$ worksheets.

c$.$ nclude links to the source data.

$9.$ Use Consolidate to create a summary of average total units shipped and total cost to date.

a$.$ Place the consolidated data on the Summary worksheet in cells B$31$:C$39.$

b$.$ Use the Average function to consolidate the data from cells B$3$:C$11$ in the $2019$ and $2020$ worksheets.

c$.$ Do not include links to the source data.

$10.$ On the Locations worksheet, use Text to Columns to separate the city and country names into separate columns.

a$.$ The city name should remain in column B$,$ and the country name should be moved to column $C.$

b$.$ Add the text Country to cell C$1.$

c$.$ O Use Format Painter to copy the formatting from cell B$1$ to cell C$1.$

$11.$ Import data from the Vaccines text file to cell A$1$ in the Vaccines worksheet. The file should be located in the folder where the data files for this project were saved. When you are finished with the import, delete the data connection. NOTE: If you are unable to find the Vaccines text file or are unable to complete this step due to your school's computer lab restrictions, download the file from the Resources link. Open the file and continue to complete step.

$12.$ Check for any possible compatibility issues in this workbook. Mac users skip this step and continue on to step $13.$

$13.q,$ Check for accessibility issues and fix all the merged cell issues found.

$14.$ Mark $15.$