Excel Assignment

Student Name:

CISA 3358 MIS, Spring 2021, Microsoft Excel – Assignment 2, Ms. Carmeshia L. Miller

Microsoft Excel - Assignment 2

Due: March 28th Task: You will modify the CISA 3358 Microsoft Excel - Assignment 2 Workbook provided in Blackboard by the professor. The modified spreadsheet should meet the requirements below. All requirements listed must be incorporated to receive full credit.

Requirements: Using the provided Microsoft Excel workbook, you will create an updated spreadsheet. The point count for each task is listed next to the task. The specific detail of each task must be exact. All fields should be populated with information, make it up if it isn't specified. (10- point deduction for any empty fields) All font should be Baskerville Old Face.

Rename the spreadsheet to “your first name last name – CISA 3358 Excel Assign 2”. Example: Carmeshia Miller – CISA 3358 – Excel Assign 2

Modify the Workbook to include all the following items/specifications:

• Workbook should consist of five (5) main sheets and a sheet for drop-down options. • You will modify the excel spreadsheet provided to have 5 sheets plus a Drop-down sheet. • The 5 sheets should have the following names:

o Player Roster o Contact Information o Uniform Sizes o Medical Information o Parents Information

• (5 points) Grid Lines – Each cell should be formatted with outline and inside borders. Sheet Contents & Requirements

• (45 points) Player Roster o (5 Points) Replace the title CISA 3358 - Assignment 2 on sheet 1 with San Antonio Soccer

Sisters & Brothers. Change the font size to 16 and bold. o (5 points) Merge and Center cells A1 – K1 & Merge and Center cells A3 with B3. o (5 points) Insert a Column between first name and age. Title the column Gender. Create a

dropdown with a selection for Male, Female, prefer not to disclose. o (5 points) Create a column titled Position. Create a dropdown box for the Position column

that contains the following options: o Goalie/Goalkeeper o Midfielder o Defender

Student Name:

CISA 3358 MIS, Spring 2021, Microsoft Excel – Assignment 2, Ms. Carmeshia L. Miller

o Forward o Center Back o Sweeper o Winger

o (5 points) Create a column and title it Club Dues. Populate the club dues. The dues for real people are $25 and the dues for fake people or cartoon characters are $45.

o (5 points) Format the column to only accept Currency values. Set it for two (2) decimal places.

o (5 points) Configure the club dues column to have a total at the end. o (5 points) Insert Rows - Insert the following players to the players roster and all other sheets.

Make sure all names are in alphabetical order. o Olivia Pope o Ronald McDonald o Roger Nelson o Roger Rabbit o Lorena Bobbitt o Boo Boo Da Fool o Christian Keyes

• (20 points) Contact Information o The first two columns of each sheet should mirror columns A and B on the Player Roster

sheet. o Place a merged and centered title in A1 – K1, title it Contact Information. Font size should be

16 and bold. o Each column header/title should be in bold, 14 pt., Baskerville Old Face font. o The first column should remain frozen. o Modify each column on all sheets so that all content can be seen. Use AutoFit Column Width. o Create fake addresses for all players. Each player’s state should be different. o Create a separate column for street number and name, City, State, and Zip Code. Format all

Zip code cells for zip code input only and validate the data so that the criteria allows text lengths equal to 5.

o Create an Input Message and Error Alert with the following information: o Input Message Title: Enter Valid Zip Code o Input Message: Please enter a valid 5-digit zip code. o Error Alert Style: Stop o Error Alert Title: Zip Code Error o Error Message: Please enter a valid 5-digit zip code. o Create a dropdown and include the abbreviations for all states.

Student Name:

CISA 3358 MIS, Spring 2021, Microsoft Excel – Assignment 2, Ms. Carmeshia L. Miller

o Create a separate column for a cell phone number. Format the cell phone column for telephone input only.

o Format all phone number cells for zip telephone input only and validate the data so that the criteria allow text lengths equal to 10.

o Create an Input Message and Error Alert with the following information: o Input Message Title: Enter Valid Phone Number o Input Message: Please enter a valid 10-digit phone number. o Error Alert Style: Warning o Error Alert Title: Phone Number Code Error o Error Message: Please enter a valid 10-digit phone number.

• (10 points) Uniform Sizes o The first two columns of each sheet should mirror columns A and B on the Player Roster

sheet. o Place a merged and centered title in A1 – K1, title it Uniforms. Font size should be 16 and

bold. o Each column header/title should be in bold, 14 pt., Baskerville Old Face font. o The first column should remain frozen. o Modify each column on all sheets so that all content can be seen. Use AutoFit Column Width. o Input uniform and shoe sizes for each player. Shoe size column should only allow for 2-digit

numbers. Enter fictitious data. o There should be a shirt column, shorts column, and shoe size column. o Create a separate dropdown box for the shirt and shorts size column that contains the

following options: • Extra Small • Small • Medium • Large • Extra Large • XX-Large • XXX-Large

• (15 points) Medical Information o The first two columns of each sheet should mirror columns A and B on the Player Roster

sheet. o Place a merged and centered title in A1 – K1, title it Medical Information. Font size should be

16 and bold. o Each column header/title should be in bold, 14 pt., Baskerville Old Face font. o The first column should remain frozen. o Modify each column on all sheets so that all content can be seen. Use AutoFit Column Width.

Student Name:

CISA 3358 MIS, Spring 2021, Microsoft Excel – Assignment 2, Ms. Carmeshia L. Miller

o Include a Date of Birth (DOB) column formatted to only accept dates. o Format DOB input to validate the data so that it only allows criteria for DOBs between

January 1st, 1996 and January 1st, 2010. o Create an Input Message and Error Alert with the following information: o Input Message Title: Enter Valid Date of Birth o Input Message: Please enter a valid DOB within the proper date range. o Error Alert Style: Stop o Error Alert Title: Date of Birth Code Error o Error Message: Please enter a DOB within the allowable range. o Include a blood type column. Format the blood type column as a dropdown with all the

legitimate blood types below: • A RhD positive (A+) • A RhD negative (A-) • B RhD positive (B+) • B RhD negative (B-) • O RhD positive (O+) • O RhD negative (O-) • AB RhD positive (AB+) • AB RhD negative (AB-)

• (10 points) Parent’s Information o The first two columns of each sheet should mirror columns A and B on the Player Roster

sheet. o Place a merged and centered title in A1 – K1, title it Parent’s Information. Font size should be

16 and bold. o Each column header/title should be in bold, 14 pt., Baskerville Old Face font. o The first column should remain frozen. o Modify each column on all sheets so that all content can be seen. Use AutoFit Column Width. o Create two columns with each players’ mother’s full name. o Create a separate column for the mother’s cell phone number. Format the cell phone column

identical to the way it is in Contact Information above. o Create two columns with each players’ father’s full name. o Create a separate column for the father’s cell phone number. Format the cell phone column

identical to the way it is in Contact Information above.