Ch 12 Grader Project Exam
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.
For the purpose of grading the project you are required to perform the following tasks:
Open the TIA_Ch10_Start.xlsx file, and save asTIA_Ch10_LastFirst. Display the DEVELOPER tab, if necessary.
On the Time Card worksheet, in cell B3 type Ted Hoyt. In cell B4, use the VLOOKUP function to lookup up the value of cell B3 in the range A2:C16 on the Employee Data worksheet, and return the value in the third column of that range. UseFALSE as the Range_Lookup argument.
In cell B5, create a VLOOKUP function to look up the value of cell B3 in the range A2:C16 on the Employee Data worksheet, and return the value from the second column. UseFALSE as the Range_Lookup argument.
On the Time Card worksheet, click cell C9, enter 4/11/2016, and then use the Fill Handle to complete the series of dates in cells C10:C13.
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.