View the step-by-step solution to:

Office 2013 - myitlab:grader - Instructions Excel Project Ch 12 Grader Project Exam Project Description: You've been asked to automate a time sheet,...

I have an excel worksheet I can not figure out.  Below I will attach the instruction file and the excel sheet to use with the instruction file.  Thank you in advance!

Ofce 2013 – myitlab:grader – Instructions Excel Project Ch 12 Grader Project Exam Project Description: You’ve been asked to automate a time sheet, and to automatically record a log of the weekly payments using Excel 2013. You have made great progress on the Time Card, and now need to add data validation, VLOOKUPs to automate Employee data entry, an IF function to calculate the daily payments, and a macro to copy the time card data to a payment log. Instructions: For the purpose o± grading the project you are required to per±orm the ±ollowing tasks: Step Instructions Points Possible 1 Open the TIA_Ch10_Start.xlsx ²le, and save as TIA_Ch10_LastFirst . Display the DEVELOPER tab, i± necessary. 0.000 2 On the Time Card worksheet, in cell B3 type Ted Hoyt . In cell B4, use the VLOOKUP ±unction to lookup up the value o± cell B3 in the range A2:C16 on the Employee Data worksheet, and return the value in the third column o± that range. Use FALSE as the Range_Lookup argument. 10.000 3 In cell B5, create a VLOOKUP ±unction to look up the value o± cell B3 in the range A2:C16 on the Employee Data worksheet, and return the value ±rom the second column. Use FALSE as the Range_Lookup argument. 10.000 4 On the Time Card worksheet, click cell C9, enter 4/11/2016 , and then use the Fill Handle to complete the series o± dates in cells C10:C13. 6.000 5 Select range D9:D13. On the DATA tab, select Data Validation. In the Data Validation dialog box, on the Settings tab, click the Allow arrow and select Decimal. Click the Data arrow, and select between; then enter 0 in the Minimum box and 12 in the Maximum box. Add a Stop Error Alert, with the title: Invalid Entry , and an Error message: Total hours worked cannot exceed 12 hours. 12.000 6 In cells D9 type 16 . In the Data Validation Error box, click Retry, and then type 10 . In cells D10:D13, type 8 , 12 , 8 , and 9 respectively. Note the Overtime Hours ²ll in automatically, calculating the hours in excess o± 8 hours. 10.000 7 In cell F9, use an IF ±unction to test i± the value in D9 is less than or equal to 8 . this condition is true, then multiply D9 times E4 (the regular rate). I± the condition is ±alse, then multiply 8 times E4 and add to that E9*E5 (the overtime rate). Use the ²ll handle to copy the ±ormula to F10:F13. Make sure you use absolute cell re±erences and parentheses in the ±ormula where needed. 10.000 8 Display the Payment Log worksheet. This is where you’ll record the payment data ±or each employee. Click cell A1. On the DEVELOPER tab, click Record Macro. Name the macro CopyTimeCard and assign a shortcut key o± CTRL+ t . 7.000 9 With the Macro recorder on, complete the ±ollowing steps: Display the Time Card worksheet, and then select and copy the range A20:F20. Display the Payment Log worksheet. On the DEVELOPER tab, in the Code group, click Use Relative Re±erences. Press END, and then DOWN ARROW two times. Click Use Relative Re±erences again. On the HOME tab, click the Paste arrow, and select Values. Click cell A1, display the Time Card worksheet, press ESC, and then click cell B3. Click the DEVELOPER tab, the click Stop recording. 7.000 Updated: 02/08/2015 1 Current_Instruction.docx
Background image of page 1
Ofce 2013 – myitlab:grader – Instructions Excel Project Step Instructions Points Possible 10 Test the Macro by typing Brett Martin in cell B3. In cells D9:D13, type 8 , 10 , 8 , 8 , and 9 , respectively. Click cell B3, then press CTRL+T to run the Macro. Display the Payment Log worksheet to ensure your data has been successFully transFerred. 16.000 11 On the DEVELOPER tab, click Macros, select the CopyTimeCard macro, and click Edit. Copy and paste the macro code to a blank worksheet. Name the worksheet Macro . Position the Macro tab to the right oF the Payment Log tab. 12.000 12 Save the workbook. Click Yes when asked to save as a Macro-Free workbook. Ensure that the worksheets are properly named and in the Following order: Employee Data, Time Card, Payment Log, and Macro. Submit based on your instructor’s directions. 0.000 Total Points 100.000 Updated: 02/08/2015 2 Current_Instruction.docx
Background image of page 2
Department Ted Hoyt Sales THS135 Bre± Mar²n Produc²on BMP246 Marina Neslin Finance MNF357 Samantha Nyce Marke²ng SNM468 Jenifer Gomez Produc²on JGP579 Alan Greer Sales AGS690 Ted Thomas Finance TTF801 Laura French Marke²ng LFM912 Carolyn Berger Sales CBS102 Patricia Sanchez Finance PSF113 Diane Wright Produc²on DWP124 Shannon Finn Sales SFS356 Spencer LeMay Finance SMF467 Autumn Sprite Marke²ng ASM578 Liu Lu Produc²on LLP689 Employee Name Employee Number
Background image of page 1
Time Card Employee Name Ted Hoyt Employee Number Regular Rate $7.50 Department Over±me Rate $11.25 Day Date Payment Monday Tuesday Wednesday Thursday Friday Weekly Total 0 0 $ - Transfer Data Week Employee Name Department Payment 12/30/1899 Ted Hoyt 0 0 0 $ - Total Hours Worked Over±me Hours Worked Employee Number Total Hours Worked
Background image of page 2
Show entire document
Sign up to view the entire interaction

Top Answer

Please check... View the full answer

TIA_Ch10_LastFirst.xlsx

Employee
Name
Ted Hoyt
Brett Martin
Marina Neslin
Samantha Nyce
Jenifer Gomez
Alan Greer
Ted Thomas
Laura French
Carolyn Berger
Patricia Sanchez
Diane Wright
Shannon Finn
Spencer LeMay
Autumn...

Sign up to view the full answer

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Ask a homework question - tutors are online