Challenge Yourself 9.4 In this project, you will use data validation to identify missing and incorrect data. You will create a summary worksheet to consolidate data about vaccine shipments, and then you will import data from the Vaccines Access database and manipulate

A Skills Approach: Excel 2016 Chapter 9: Importing Data, Reviewing,  and Finalizing the Workbook  

Challenge Yourself 9.4

In this project, you will use data validation to identify missing and incorrect data. You will create a summary worksheet to consolidate data about vaccine shipments, and then you will import data from the Vaccines Access database and manipulate the data.

Skills needed to complete this project:

  • Adding Data Validation
  • Working with Comments
  • Protecting Worksheets and Workbooks from Changes
  • Creating a Drop-Down List for Data Entry
  • Inserting Hyperlinks
  • Consolidating Data in a Summary Worksheet
  • Converting Data to Columns
  • Importing Data from Access
  • Checking for Compatibility with Previous Versions of Excel
  • Finalizing the Workbook  

Important: Download the resource file(s) needed for this project from the Resources link. Make sure to extract the file(s) after downloading the resources zipped folder. Visit the SIMnet instant help for step-by-step instruction.  

  1. Open the start file EX2016-ChallengeYourself-9-4. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it  
  2. If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the top of the workbook so you can modify the workbook.
  3. Apply data validation rules to cells F15:F24 on the 2016 Shipments worksheet and cells F15:F26 on the 2017 Shipments worksheet.
    1. Require whole numbers between 500 and 5,000.
    1. Do not allow blanks.
    1. Circle invalid data in both the 2016 Shipments and 2017 Shipments worksheets.
  4. Add this comment to cell F20 on the 2017 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 2016 Shipments worksheet and one cell on the 2017 Shipments worksheet. Use the data validation circles to find them.
  6. Apply data validation rules to cells C15:C24 on the 2016 Shipments worksheet and cells C15:C26 on the 2017 Shipments worksheet.
    1. Values for these cells must come from the Locations named range.
    1. Include an in-cell drop-down list. Do not allow blanks.
    1. Circle invalid data in both the 2016 Shipments and 2017 Shipments worksheets.

1 | Page                                                                             Challenge Yourself 9.4                                                         Last Updated 12/11/17

A Skills Approach: Excel 2016                                                                                                               Chapter 9: Importing Data, Reviewing,  

and Finalizing the Workbook  

  • Add a hyperlink to cell C16 on the 2016 Shipments worksheets to link to cell A26 on the Locations worksheet. Test the link.
  • Use Consolidate to create a summary of total units shipped and total cost to date.
    • Place the consolidated data on the Summary worksheet in cells B2:C10.
    • Use the Sum function to consolidate the data from cells B3:C11 in the 2016 and 2017 worksheets. c.  Include links to the source data.
  • Use Consolidate to create a summary of average total units shipped and total cost to date.
    • Place the consolidated data on the Summary worksheet in cells B31:C39.
    • Use the Average function to consolidate the data from cells B3:C11 in the 2016 and 2017 worksheets. c.  Do not  include links to the source data.
  • On the Locations worksheet, use Text to Columns to separate the city and country names into separate columns.
    • 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 C1.

c.  Use Format Painter to copy the formatting from cell B1 to cell C1.

  1. Import data from the Vaccines table in the Vaccines Access database to cell A1 in the Vaccines worksheet. The database 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.

  1. Save and close the workbook.
  1. Upload and save your project file 14.  Submit project for grading.

2 | Page                                                                             Challenge Yourself 9.4                                                         Last Updated 12/11/17

Do you need any assistance with this question?
Send us your paper details now
We'll find the best professional writer for you!

Add a Comment