reddfox5002
New member
- Joined
- Apr 16, 2014
- Messages
- 1
- Reaction score
- 0
- Points
- 0
Hello all
I am modifying timesheets for the hourly employees for our company. Each employee records their time on a weekly basis in Excel. In each week, there are two tables in which the employees record their time. The first table is for regular working hours, the second is for overtime hours.
I would like data validation to give an error message if the total regular hours go over 40 for the week (to make sure that any time over 40 gets put in the overtime table). There is a cell outside table 1 that has a formula that calculates the total hours entered in table 1.
Lets say the top left corner of table 1 where employees enter their time is G44, and the bottom right corner is M58. The cell that totals the time is N59. So I would like data validation to watch N59, and if any value is entered in the table (G44 to M58) that makes N59 go over 40, I would like it to give an error message and have the employee correct the time.
So as an example, if John has 8 hours entered for Monday through Thursday, and then accidentally enters a 9 anywhere in the table for Friday, I would like the error message to pop up.
Is this something data validation can do, or does this require a macro? Thanks in advance for any help.
I am modifying timesheets for the hourly employees for our company. Each employee records their time on a weekly basis in Excel. In each week, there are two tables in which the employees record their time. The first table is for regular working hours, the second is for overtime hours.
I would like data validation to give an error message if the total regular hours go over 40 for the week (to make sure that any time over 40 gets put in the overtime table). There is a cell outside table 1 that has a formula that calculates the total hours entered in table 1.
Lets say the top left corner of table 1 where employees enter their time is G44, and the bottom right corner is M58. The cell that totals the time is N59. So I would like data validation to watch N59, and if any value is entered in the table (G44 to M58) that makes N59 go over 40, I would like it to give an error message and have the employee correct the time.
So as an example, if John has 8 hours entered for Monday through Thursday, and then accidentally enters a 9 anywhere in the table for Friday, I would like the error message to pop up.
Is this something data validation can do, or does this require a macro? Thanks in advance for any help.