MOS19_XL_Expert_PROJ1 - Manage 1.1 Project Description: In this project, you will manage workbook options and settings to meet objectives 1.1 through 1.3 of the Excel MOS Expert exam. To complete...

1 answer below »


MOS19_XL_Expert_PROJ1 - Manage 1.1






Project Description:


In this project, you will manage workbook options and settings to meet objectives 1.1 through 1.3 of the Excel MOS Expert exam. To complete the project, you will manage workbooks 1.1, prepare workbooks for collaboration 1.2, and use and configure language options 1.3. Note: This project was created for PC users since the MOS Exam is completed in the PC user environment.





Steps to Perform:










































































Step




Instructions




Points Possible



1



Start Excel. Download and open the file named
Student_MOS19_XL_Expert_PROJ1_Manage.xlsx.
Save the file as
Last_First_MOS19_XL_Expert_PROJ1_Manage.



0



2



Create a connection to the Friday worksheet in the
MOS19_XL_Expert_PROJ1_Friday.xlsx
file. Load the data as a new worksheet and rename it as
Friday. Move the worksheet after Thurs in the workbook. Close the Queries & Connections pane. Save the file.




10



3



Use Excel Options to show the Developer tab on the ribbon. Enable all macros in the workbook. Record a macro named
Heading. Follow these steps in the Friday worksheet:

* Insert three blank rows 1:3.

* Copy A1:A2 from the Wed worksheet and use Paste Using Source column widths to A1 in the Friday worksheet.

* Replace A2 text with
Friday, and then stop recording.
Save the file as a macro enabled workbook using the same filename. Open the
Last_First_MOS19_XL_Expert_PROJ1_Manage.xlsx
file.

Copy the Module1 macro from the
Last_First_MOS19__XL_Expert_PROJ1_Manage.xlsm
file to the Visual Basic editor in the
Last_First_MOS19_XL_ Expert_PROJ1_Manage.xlsx.


Run the Heading macro in the Excel workbook.

In VBE, delete any empty lines or lines (‘) in the Module1 code. Copy the code and paste the code in cell A1 of the Macro sheet in the
Last_First_MOS19_XL_Expert_PROJ1_Manage.xlsx
workbook.

Save the workbook as a macro free workbook. Close the Visual Basic window and close the macro enabled workbook.




20



4



Apply the password
Protected!
to the Macro worksheet to protect the worksheet and locked cells.



5



5



On the Wed worksheet, replace the sales for Gayle Youngest with
4150.00. Update the Calculation Options to automatically update. Show the comment in F4 and then reply to the comment
These have been reviewed and are correct.
(include the period).



15



6



On the Summary worksheet, create a formula in cell B5 that provides the total number of Bookings for Thursday and includes the structured reference for the Thursday table.

Repeat this technique to create the structured reference for total sales in cell C5.

Create a formula in B6 that refers to cell C58 in the
MOS19_XL_Expert_PROJ1_Friday.xlsx
workbook.

Create a formula in C6 that refers to cell D58 using the file in the previous step. Save the file.



10



7



Using the Task Manager, use End Task to close Excel. Start Excel and show recovered files. Use the Snipping Tool to create a rectangular screenshot of the Document Recovery Task pane and the recovered
Last_First_MOS19_XL_Expert_PROJ1_Manage.xlsx
file.

Open the recovered file. Copy and paste the snip to cell A1 in the Recovery worksheet. Close the Document Recovery task pane. Save the file.



10



8



Save the file as an Excel Template with the name
Last_First_MOS19_XL_Expert_PROJ1_Template.xltx, accept to refresh the external data.

Open the
Last_First_MOS19_XL_Expert_PROJ1_Manage.xlsx
file. Create a workbook connection to the template file. Close the Query Editor and keep the changes.

Rename Sheet1 as
Template
and ensure it's located as the last sheet in the workbook.
Close Queries & Connections pane. Save the file. Close the template file.




10



9



On the Summary worksheet, restrict editing of the formulas in B4:C6. Enter the Title
Protect Range
and use the password
Protected!
Paste the permission information into a new workbook.

Copy the range A1:D4 and then paste to cell A8 in the Summary worksheet. Close Book1.



5



10



Encrypt the workbook with the password
Protected! Use the Snipping Tool to take a rectangular snip of the Protect Workbook yellow highlighted area. Copy the snip and then remove the workbook password encryption. Paste the snip to the Encrypt worksheet in cell A1.



10



11



Protect the structure of the workbook with the password
Protected! Close the file.



5



12



Ensure the worksheets are in the following order: Summary, Wed, Thurs, Friday, Macro, Recovery, Encrypt, and Template. Save and close
Last_First_MOS19_XL_Expert_PROJ1_Manage.xlsx. Exit Excel. Submit the file as directed.



0












Total Points




100



mos19xlexpertproj1-manage-11instructions-igwem4zq.docx mos19xlexpertproj1friday-aqmo4r50.xlsx renterosmos19xlexpertproj1manage-r2ry5c0c.xlsx 2nd-assignment-uelzh0l5-3ygnchlz.zip mos19xlexpertproj1-manage-11instructions-22isnfzd-uamvjucu.docx
Answered 1 days AfterJul 23, 2021

Answer To: MOS19_XL_Expert_PROJ1 - Manage 1.1 Project Description: In this project, you will manage workbook...

Ali Asgar answered on Jul 24 2021
141 Votes
Renteros_MOS19_XL_Expert_PROJ1_Manage.xlsx
Summary
        Bookings Summary
        Day        Bookings        Sales
        Wednesday        321        $ 113,655.00
        Thursday        307        $ 110,825.00
        Friday        284        $ 195,560.00
        Permissions for        [R_Enteros_MOS19_XL_Expert_PROJ1_Manage.xlsx]Summary
        Range Title        Range of Cells        Password Protected        Users and Groups
        Protect Range        =$A$4:$C$6        Yes        -
Wed
        Daily Summary
        Wednesday
        Last        First        Bookings        Sales        Location        Comments
tc={060F59DA-2F9A-4DF7-AD9E-204841F97626}: [Threaded comment]
Your version of Excel allows you to read this threaded comment; however, any edits to it will get removed if the file is opened in a newer version of Excel. Learn more: https://go.microsoft.com/fwlink/?linkid=870924
Comment:
Check to verify these are correct.
Reply:
These have been reviewed and are correct.        Employee #        Phone        Commission?
        Anderson        Tip        8        $ 3,400.00        St. Cloud                9403048        (612) 555-3463
        Beau        Tim        7        $ 2,540.00        Boston                81048830        (218) 555-4055
        Bern        Jules        7        $ 2,450.00        Minneapolis                75661036        (612) 555-4586        Yes
        Blick        Loger        3        $ 1,005.00        Two Harbors                75825555        (218) 555-2345        Yes
        Blings        Sten        4        $ 1,040.00        Aspen Falls                81235205        (612) 555-4958        Yes
        Brown        Joe        2        $ 600.00        Bloomington                20830283        (612) 555-2934
        Boski        Jenifer        2        $ 635.00        Aspen Falls                73501020        (612) 555-3928        Yes
        Brad        Nancy        1        $ 450.00        Mankato                31346341        9548        Yes
        Burr        Arons        0        $ - 0        Rochester        Sick        21349282        2395
        Cheng        Anne        5        $ 1,750.00        Maplewood                23890626        (612) 555-3474        Yes
        Chikov        Toni        13        $ 4,550.00        Edina                2332093        (612) 555-4583        Yes
        Clays        Darla        6        $ 2,100.00        St. Paul                63091818        (612) 555-4096
        Ellor        Nanci        7        $ 2,450.00        Fargo                4593311        (612) 555-9855
        Ericks        Shelby        9        $ 3,150.00        St. Cloud                57349411        (218) 555-9567
        Frick        Luriel        5        $ 1,410.00        Aspen Falls                96333334        (612) 555-3934
        Fuentes        Bernard        2        $ 700.00        St. Paul                50232301        (612) 555-5659        Yes
        Graham        Phil        5        $ 1,750.00        Edina                68235239        (612) 555-9767
        Grimm        Lucille        7        $ 2,450.00        Rochester                58732332        7985        Yes
        Harrys        Jon        5        $ 2,175.00        Blaine                96340342        (612) 555-6735
        Hill        Trent        11        $ 3,850.00        St. Louis Park                36039494        (612) 555-8679
        James        Jen        5        $ 1,750.00        St. Peter                06934949        5967        Yes
        Jordon        James        10        $ 3,500.00        Spring Lake Park                96349693        (612) 555-2346        Yes
        Kaid        Jonathan        10        $ 3,500.00        Brooklyn Center                82392346        (612) 555-4574        Yes
        Kippert        Bryan        1        $ 350.00        Duluth                43234596        (218) 555-8456
        Kulhavy        Marti        11        $ 3,850.00        Minneapolis                46594204        (612) 555-4506        Yes
        Landgo        Lou        0        $ - 0        Fargo        vacation        76304344        (612) 555-4096
        Lindgram        Elsie        11        $ 3,850.00        Bloomington                03969434        (612) 555-2347        Yes
        Lindquist        Shelia        4        $ 1,400.00        Edina                92030303        (612) 555-3456        Yes
        Lyons        Rachel        6        $ 2,100.00        Maplewood                0938233        (612) 555-4564
        Nelson        Wendy        12        $ 4,200.00        Two Harbors                06934484        (218) 555-5687        Yes
        Nordstrom        Terry        9        $ 3,150.00        Duluth                0683034        (218) 555-5409
        Norton        LeAnne        10        $ 3,500.00        St. Paul                96838443        (612) 555-3456        Yes
        Olness        Janelle        3        $ 1,050.00        Fargo                09384209        (612) 555-4592
        Olson        Anne        1        $ 350.00        Brooklyn Center                49850394        (612) 555-3456        Yes
        Parmont        Tim        9        $ 3,150.00        Brainerd                49083634        (612) 555-4534        Yes
        Peterson        Ruth        3        $ 1,050.00        St. Cloud                4894883        (218) 555-7965
        Rodriguez        Maria        0        $ - 0        Hibbing        Sick        7485973        (218) 555-5679        Yes
        Rommero        Antonio        10        $ 3,500.00        Spring Lake Park                95069056        (612) 555-4564
        Samuals        Brian        8        $ 2,800.00        Minneapolis                56878345        (612) 555-4567        Yes
        Smith        John        4        $ 1,400.00        Minneapolis                45983796        (612) 555-2437
        Smith        Richard        1        $ 350.00        Blaine                70953744        (612) 555-4564
        Svens        Lois        4        $ 1,400.00        Mankato                09847551        (612) 555-1833        Yes
        Swanson        Julie        2        $ 700.00        St. Peter                45789561        4075
        Therman        Ruth        10        $ 3,500.00        Bloomington                96394844        (612) 555-4567        Yes
        Thomas        Amy        7        $ 2,450.00        Edina                56454226        (612) 555-2845
        Thompson        Harrison        9        $ 3,150.00        Duluth                03495384        (218) 555-5098        Yes
        Thorgood        Bruce        3        $ 1,050.00        Two Harbors                34527445        (218) 555-3467        Yes
        Timscleft        Nancy        6        $ 2,100.00        Rochester                24545741        8059
        Torkleson        Anna        0        $ - 0        Brainerd        On vacation        84845771        (218) 555-3456
        Trent        Jason        9        $ 3,150.00        Hibbing                39820981        (218) 555-4499        Yes
        Wilson        Roger        5        $ 1,750.00        Brooklyn Center                89468491        (612) 555-3456
        Worgot        Mary        9        $ 3,150.00        Rochester                80954455        (612) 555-4956        Yes
        Wu        Amy        4        $ 1,400.00        St. Paul                37483236        (612) 555-8765        Yes
        Wyatt        Maria        7        $ 2,450.00        Minneapolis                84598657        (612) 555-5678
        Young        Bernice        0        $ - 0        Duluth        Sick        97435456        (218) 555-4303        Yes
        Youngest        Gayle        9        $ 4,150.00        Minneapolis                50983541        (612) 555-6578        Yes
                        Daily Total        $ 113,655.00
&D    &F    &A
Thurs
        Daily Summary
        Thursday
        Last        First        Bookings        Sales        Location        Comments        Employee #        Phone        Commission?
        Anderson        Tip        3        $ 1,800.00        St. Cloud                9403048        (612) 555-3463
        Beau        Tim        5        $ 2,000.00        Boston                81048830        (218) 555-4055
        Bern        Jules        1        $ 1,300.00        Minneapolis                75661036        (612) 555-4586        Yes
        Blick        Loger        3        $ 1,005.00        Two Harbors                75825555        (218) 555-2345        Yes
        Blings        Sten        4        $ 1,040.00        Aspen Falls                81235205        (612) 555-4958        Yes
        Brown        Joe        2        $ 600.00        Bloomington                20830283        (612) 555-2934
        Boski        Jenifer        2        $ 635.00        Aspen Falls                73501020        (612) 555-3928        Yes
        Brad        Nancy        1        $ 450.00        Mankato                31346341        9548        Yes
        Burr        Arons        4        $ 1,560.00        Rochester                21349282        2395
        Cheng        Anne        5        $ 1,750.00        Maplewood                23890626        (612) 555-3474        Yes
        Chikov        Toni        8        $ 4,550.00        Edina                2332093        (612) 555-4583        Yes
        Clays        Darla        6        $ 2,100.00        St. Paul                63091818        (612) 555-4096
        Ellor        Nanci        7        $ 2,450.00        Fargo                4593311        (612) 555-9855
        Ericks        Shelby        9        $ 3,150.00        St. Cloud                57349411        (218) 555-9567
        Frick        Luriel        5        $ 1,410.00        Aspen Falls                96333334        (612) 555-3934
        Fuentes        Bernard        2        $ 700.00        St. Paul                50232301        (612) 555-5659        Yes
        Graham        Phil        5        $ 1,750.00        Edina                68235239        (612) 555-9767
        Grimm        Lucille        7        $ 2,450.00        Rochester                58732332        7985        Yes
        Harrys        Jon        5        $ 2,175.00        Blaine                96340342        (612) 555-6735
        Hill        Trent        11        $ 3,850.00        St. Louis Park                36039494        (612) 555-8679
        James        Jen        5        $ 1,750.00        St. Peter                06934949        5967        Yes
        Jordon        James        10        $ 3,500.00        Spring Lake Park                96349693        (612) 555-2346        Yes
        Kaid        Jonathan        10        $ 3,500.00        Brooklyn Center                82392346        (612) 555-4574        Yes
        Kippert        Bryan        1        $ 350.00        Duluth                43234596        (218) 555-8456
        Kulhavy        Marti        11        $ 3,850.00        Minneapolis                46594204        (612) 555-4506        Yes
        Landgo        Lou        0        $ - 0        Fargo        vacation        76304344        (612) 555-4096
        Lindgram        Elsie        11        $ 3,850.00        Bloomington                03969434        (612) 555-2347        Yes
        Lindquist        Shelia        4        $ 1,400.00        Edina                92030303        (612) 555-3456        Yes
        Lyons        Rachel        6        $ 2,100.00        Maplewood                0938233        (612) 555-4564
        Nelson        Wendy        12        $ 4,200.00        Two Harbors                06934484        (218) 555-5687        Yes
        Nordstrom        Terry        9        $ 3,150.00        Duluth                0683034        (218) 555-5409
        Norton        LeAnne        10        $ 3,500.00        St. Paul                96838443        (612) 555-3456        Yes
        Olness        Janelle        3        $ 1,050.00        Fargo                09384209        (612) 555-4592
        Olson        Anne        1        $ 350.00        Brooklyn Center                49850394        (612) 555-3456        Yes
        Parmont        Tim        9        $ 3,150.00        Brainerd                49083634        (612) 555-4534        Yes
        Peterson        Ruth        3        $ 1,050.00        St. Cloud                4894883        (218) 555-7965
        Rodriguez        Maria        0        $ - 0        Hibbing        Sick        7485973        (218) 555-5679        Yes
        Rommero        Antonio        10        $ 3,500.00        Spring Lake Park                95069056        (612) 555-4564
        Samuals        Brian        8        $ 2,800.00        Minneapolis                56878345        (612) 555-4567        Yes
        Smith        John        4        $ 1,400.00        Minneapolis                45983796        (612) 555-2437
        Smith        Richard        1        $ 350.00        Blaine                70953744        (612) 555-4564
        Svens        Lois        4        $ 1,400.00        Mankato                09847551        (612) 555-1833        Yes
        Swanson        Julie        2        $ 700.00        St. Peter                45789561        4075
        Therman        Ruth        10        $ 3,500.00        Bloomington                96394844        (612) 555-4567        Yes
        Thomas        Amy        7        $ 2,450.00        Edina                56454226        (612) 555-2845
        Thompson        Harrison        9        $ 3,150.00        Duluth                03495384        (218) 555-5098        Yes
        Thorgood        Bruce        3        $ 1,050.00        Two Harbors                34527445        (218) 555-3467        Yes
        Timscleft        Nancy        6        $ 2,100.00        Rochester                24545741        8059
        Torkleson        Anna        0        $ - 0        Brainerd        vacation        84845771        (218) 555-3456
        Trent        Jason        9        $ 3,150.00        Hibbing                39820981        (218) 555-4499        Yes
        Wilson        Roger        5        $ 1,750.00        Brooklyn Center                89468491        (612) 555-3456
        Worgot        Mary        9        $ 3,150.00        Rochester                80954455        (612) 555-4956        Yes
        Wu        Amy        4        $ 1,400.00        St. Paul                37483236        (612) 555-8765        Yes
        Wyatt        Maria        7        $ 2,350.00        Minneapolis                84598657        (612) 555-5678
        Young        Bernice        0        $ - 0        Duluth        Sick        97435456        (218) 555-4303        Yes
        Youngest        Gayle        9        $ 3,150.00        Minneapolis                50983541        (612) 555-6578        Yes
                        Daily Total        $ 110,825.00
&D    &F    &A
Friday
        Daily Summary
        Friday
        Last        First        Bookings        Sales        Location        Comments        Employee #        Phone        Commission?
        Anderson        Tip        8        2720        St. Cloud                9403048        (612) 555-3928        Yes
        Beau        Tim        5        1700        Boston                81048830        3934
        Bern        Jules        12        4080        Minneapolis                75661036        4055
        Blick        Loger        7        2380        Two Harbors                75825555        2345        Yes
        Blings        Sten        3        1020        Aspen Falls                81235205        4958        Yes
        Brown        Joe        0        0        Bloomington                20830283        2934
        Boski        Jenifer        4        1360        Aspen Falls                73501020        3463
        Brad        Nancy        6        2040        Mankato                31346341        9548        Yes
        Burr        Arons        0        0        Rochester        vacation        21349282        2395
        Cheng        Anne        3        1020        Maplewood                23890626        3474        Yes
        Chikov        Toni        3        100020        Edina                2332093        4583        Yes
        Clays        Darla        2        680        St. Paul                63091818        4096
        Ellor        Nanci        12        4080        Fargo                4593311        9855
        Ericks        Shelby        9        3060        St. Cloud                57349411        9567
        Frick        Luriel        2        680        Aspen Falls                96333334        4586        Yes
        Fuentes        Bernard        2        680        St. Paul                50232301        5659        Yes
        Graham        Phil        9        3060        Edina                68235239        9767
        Grimm        Lucille        6        2040        Rochester                58732332        7985        Yes
        Harrys        Jon        4        1360        Blaine                96340342        6735
        Hill        Trent        2        680        St. Louis Park                36039494        8679
        James        Jen        3        1020        St. Peter                6934949        5967        Yes
        Jordon        James        6        2040        Spring Lake Park                96349693        2346        Yes
        Kaid        Jonathan        1        340        Brooklyn Center                82392346        4574        Yes
        Kippert        Bryan        10        3400        Duluth                43234596        8456
        Kulhavy        Marti        6        2040        Minneapolis                46594204        4506        Yes
        Landgo        Lou        0        0        Fargo                76304344        4096
        Lindgram        Elsie        2        680        Bloomington                3969434        2347        Yes
        Lindquist        Shelia        3        1020        Edina                92030303        3456        Yes
        Lyons        Rachel        9        3060        Maplewood                938233        4564
        Nelson        Wendy        1        340        Two Harbors                6934484        5687        Yes
        Nordstrom        Terry        6        2040        Duluth                683034        5409
        Norton        LeAnne        12        4080        St. Paul                96838443        3456        Yes
        Olness        Janelle        3        1020        Fargo                9384209        4592
        Olson        Anne        5        1700        Brooklyn Center                49850394        3456        Yes
        Parmont        Tim        1        340        Brainerd                49083634        4534        Yes
        Peterson        Ruth        5        1700        St. Cloud                4894883        7965
        Rodriguez        Maria        0        0        Hibbing        Sick        7485973        5679        Yes
        Rommero        Antonio        2        680        Spring Lake Park                95069056        4564
        Samuals        Brian        3        1020        Minneapolis                56878345        4567        Yes
        Smith        John        4        1360        Minneapolis                45983796        2437
        Smith        Richard        1        340        Blaine                70953744        4564
        Svens        Lois        3        1020        Mankato                9847551        1833        Yes
        Swanson        Julie        11        3740        St. Peter                45789561        4075
        Therman        Ruth        9        3060        Bloomington                96394844        4567        Yes
        Thomas        Amy        2        680        Edina                56454226        2845
        Thompson        Harrison        3        1020        Duluth                3495384        5098        Yes
        Thorgood        Bruce        6        2040        Two Harbors                34527445        3467        Yes
        Timscleft        Nancy        8        2720        Rochester                24545741        8059
        Torkleson        Anna        0        0        Brainerd        vacation        84845771        3456
        Trent        Jason        8        2720        Hibbing                39820981        4499        Yes
        Wilson        Roger        9        3060        Brooklyn Center                89468491        3456
        Worgot        Mary        12        4080        Rochester                80954455        4956        Yes
        Wu        Amy        10        3400        St. Paul                37483236        8765        Yes
        Wyatt        Maria        6        2040        Minneapolis                84598657        5678
        Young        Bernice        8        2720        Duluth                97435456        4303        Yes
        Youngquist        Gayle        7        2380        Minneapolis                50983541        6578        Yes
        Daily Total                284        195560
Macro
        Sub Heading()
         Rows("1:1").Select
         Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
         Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
         Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
         Sheets("Wed").Select
         Range("A1:I2").Select
         Selection.Copy
         Sheets("Friday").Select
         Range("A1").Select
         ...
SOLUTION.PDF
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here
Have files?