{"id":23211,"date":"2022-01-06T03:45:27","date_gmt":"2022-01-06T03:45:27","guid":{"rendered":"https:\/\/assignmenttutors.net\/blog\/excel-2019-in-practice-ch-7-independent-project-7-6\/"},"modified":"2022-01-06T03:45:27","modified_gmt":"2022-01-06T03:45:27","slug":"excel-2019-in-practice-ch-7-independent-project-7-6","status":"publish","type":"post","link":"https:\/\/assignmenttutors.net\/blog\/excel-2019-in-practice-ch-7-independent-project-7-6\/","title":{"rendered":"Excel 2019 in practice – ch 7 independent project 7-6"},"content":{"rendered":"
\n

\u00a0<\/p>\n

The Hamilton Civic Center is developing a template for member exercise and off-site seminars. You create the template, set validation, enter formulas, and insert a combo box control. You then create a new workbook from the template.<\/p>\n

[Student Learning Outcomes 7.1, 7.2, 7.3, 7.4, 7.6]<\/strong><\/p>\n

File Needed: HamiltonCC-07.xlsx<\/strong><\/em> (Available from the<\/em> Start File link.)<\/em><\/p>\n

Completed Project File Names: [your name]-HamiltonCC-07.xlsx<\/strong><\/em><\/p>\n

Skills Covered in This Project<\/strong><\/em><\/h3>\n
    \n
  • Set data validation to use a list.<\/li>\n
  • Set validation to restrict dates.<\/li>\n
  • Create an error alert message.<\/li>\n
  • Insert a combo box control.<\/li>\n
  • Check accessibility and add alt text.<\/li>\n
  • Protect a worksheet.<\/li>\n<\/ul>\n
      \n
    1. Open the HamiltonCC-07<\/strong><\/em> workbook and click the Enable Editing<\/strong> button. The file will be renamed automatically to include your name.<\/li>\n
    2. Select cell D2<\/strong> on the Data<\/strong> sheet and review the formula. The formula divides calories by time and rounds the results to three decimal places.\"Images\"Figure 7-69 Custom format for values<\/strong><\/li>\n
    3. Select cells D2:D10<\/strong> and open the Format Cells<\/em> dialog box.<\/li>\n
    4. Select the Custom<\/strong> category on the Number<\/em> tab and build a format to display three decimal places (Figure 7-69).<\/li>\n
    5. Select cell D2<\/strong> and click the Format Painter<\/strong> button [Home<\/em> tab, Clipboard<\/em> group]. Click the Calorie Tracking<\/strong> tab and paint the format to cells E3:E33<\/strong>.\"Images\"Figure 7-70<\/strong> VLOOKUP<\/strong><\/em> formula<\/strong><\/li>\n
    6. Select cell E3<\/strong> on the Calorie Tracking<\/strong> sheet. Start a VLOOKUP<\/em> function to lookup the label in cell C3<\/strong>. For the array, use an absolute reference to cells A2:D10<\/strong> on the Data<\/strong> sheet. The Col_index_num<\/em> is 4<\/strong> for the calories per minute column. Leave the Range_lookup<\/em> argument empty; the array (A2:D10) is sorted in ascending order. The result of the VLOOKUP<\/em> formula is calories per minute for the exercise (Figure 7-70).<\/li>\n
    7. Edit the formula in cell E3<\/strong> to multiply the results by the number of minutes in cell D3<\/strong>.<\/li>\n
    8. Copy the formula in cell E3<\/strong> to cells E4:E33<\/strong>. The #N\/A error message displays in rows where no data displays.<\/li>\n
    9. Select cells C3:C33<\/strong> and set data validation to use the list of activity names on the Data<\/strong> sheet. Do not use an input message or an error alert.<\/li>\n
    10. Select the Calorie Tracking<\/strong> sheet and delete the data in cells A3:D23<\/strong>.\"Images\"Figure 7-71 Data validation for dates<\/strong><\/li>\n
    11. Select cells B3:B33<\/strong> and set data validation to use a Date<\/strong> that is less than or equal to TODAY<\/em> (Figure 7-71). Include a Stop<\/em> error alert with a title of Check Date<\/strong> and a message of Date must be today or in the past.<\/strong> including the period.<\/li>\n
    12. Select cells A3:D33<\/strong> and remove the Locked<\/strong> cell property. Select cell A3<\/strong> to position the insertion point.<\/li>\n
    13. Display the Developer<\/strong> tab on the Ribbon<\/em> and click the Data<\/strong> worksheet tab.\"Images\"Figure 7-72 Combo box settings<\/strong><\/li>\n
    14. Draw a combo box control to cover cell F8<\/strong> and open its Format Control<\/em> dialog box. Select cells G8:G11<\/strong> for the Input range<\/em> and type f8<\/strong> in the Cell link<\/em> box (Figure 7-72).<\/li>\n
    15. Deselect the control and then select Second<\/strong> from the control. The linked cell is under the control and hidden from view.<\/li>\n
    16. Click the Hospital Seminars<\/strong> tab and select cell D4<\/strong>. This cell has Center Across Selection<\/em> alignment applied.<\/li>\n
    17. Select cell D4<\/strong> and use CONCAT<\/em> and INDEX<\/em> to display the result from the combo box, concatenating the Index<\/em> results to the word \u201cQuarter.\u201d\n
        \n
      1. Start a CONCAT<\/em> function [Text<\/em> group].<\/li>\n
      2. Use the INDEX<\/em> function with the first arguments list as the Text1<\/em> argument.\"Images\"Figure 7-73<\/strong> INDEX<\/strong><\/em> is nested within<\/strong> CONCAT<\/strong><\/em><\/li>\n
      3. Choose cells G8:G11<\/strong> on the Data<\/em> sheet for the Array<\/em> argument and cell F8<\/strong> for the Row_num<\/em> argument. You can select the combo box control or type f8<\/strong> after the sheet name (Figure 7-73). When the array is one column, a Column_num<\/em> argument is not necessary.<\/li>\n
      4. Click between the two ending parentheses in the Formula<\/em> bar to return to the CONCAT<\/em> arguments and type a comma (,) to move to the Text2<\/em> argument. (If you accidentally click OK<\/strong>, click the Insert Function<\/strong> button to re-open the Function Arguments<\/em> dialog box.)\"Images\"Figure 7-74 Space character is included with Text2 argument<\/strong><\/li>\n
      5. Click the Text2<\/strong> box, press Spacebar<\/strong>, type Quarter<\/strong>, and click OK<\/strong>. (Figure 7-74).<\/li>\n
      6. Format cell D4<\/strong> as bold italic 16 pt<\/strong>.<\/li>\n<\/ol>\n<\/li>\n
      7. Select the Data<\/strong> sheet, select Third<\/strong> from the combo box control, and return to the Hospital Seminars<\/strong> sheet to see the results.<\/li>\n
      8. Select cell D4<\/strong> and cells D6:G10<\/strong> on the Hospital Seminars<\/strong> sheet and remove the Locked<\/strong> property.<\/li>\n
      9. Delete the contents of cells D6:G10<\/strong> and select cell D6<\/strong>.<\/li>\n
      10. Check accessibility and add the alternative text Hamilton Civic Center Logo<\/strong> to both pictures in the workbook.<\/li>\n
      11. Select cell D6<\/strong> on the Hospital Seminars<\/strong> sheet and cell A3<\/strong> on the Calorie Tracking<\/strong> sheet.<\/li>\n
      12. Protect the Hospital Seminars<\/strong> sheet and the Calorie Tracking<\/strong> sheet, both without passwords.<\/li>\n
      13. Save and close the workbook (Figure 7-75).<\/li>\n
      14. Upload and save your project file.<\/li>\n
      15. Submit project for grading.<\/li>\n<\/ol>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

        \u00a0 The Hamilton Civic Center is developing a template for member exercise and off-site seminars. You create the template, set validation, enter formulas, and insert a combo box control. You then create a new workbook from the template. [Student Learning Outcomes 7.1, 7.2, 7.3, 7.4, 7.6] File Needed: HamiltonCC-07.xlsx (Available from the Start File link.) […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"class_list":["post-23211","post","type-post","status-publish","format-standard","hentry","category-education"],"_links":{"self":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/posts\/23211","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/comments?post=23211"}],"version-history":[{"count":0,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/posts\/23211\/revisions"}],"wp:attachment":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/media?parent=23211"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/categories?post=23211"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/tags?post=23211"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}