Data Validation using whole numbers from formulas
I have a spreadsheet that our different locations used for scheduling. For
each day of the week, the manager selects a start time and an end time. The total hours then calculates in the last column using a SUM formula. What has been requested is that an error pop up when that SUM forumula calculates hours that are greater than 40. I've tried every variation in data validation that I can think of but I can't get anything to work. Any suggestions welcome. I am working in Excel 2002. Thanks! |
Data Validation using whole numbers from formulas
You could try using an If statement in the column next to the sum column that
will show if the amount is over 40 hours: Example.......if(B240,"problem," ") This formula will show the word problem when it is over 40, and nothing if it is under 40. "brodiemac" wrote: I have a spreadsheet that our different locations used for scheduling. For each day of the week, the manager selects a start time and an end time. The total hours then calculates in the last column using a SUM formula. What has been requested is that an error pop up when that SUM forumula calculates hours that are greater than 40. I've tried every variation in data validation that I can think of but I can't get anything to work. Any suggestions welcome. I am working in Excel 2002. Thanks! |
Data Validation using whole numbers from formulas
=SUM($A$1:$A$5)<=--"40:00"
I assume you apply it in the cell where the end time is entered of course if you are not using time values use <=40 instead -- Regards, Peo Sjoblom http://nwexcelsolutions.com "brodiemac" wrote in message ... I have a spreadsheet that our different locations used for scheduling. For each day of the week, the manager selects a start time and an end time. The total hours then calculates in the last column using a SUM formula. What has been requested is that an error pop up when that SUM forumula calculates hours that are greater than 40. I've tried every variation in data validation that I can think of but I can't get anything to work. Any suggestions welcome. I am working in Excel 2002. Thanks! |
Data Validation using whole numbers from formulas
Your suggestion didn't work until I tried to remove the <=--"40:00", then the
validation messages would pop up. Strange. Also, just as an FYI, the totals display in whole numbers, not in a time format. "Peo Sjoblom" wrote: =SUM($A$1:$A$5)<=--"40:00" I assume you apply it in the cell where the end time is entered of course if you are not using time values use <=40 instead -- Regards, Peo Sjoblom http://nwexcelsolutions.com "brodiemac" wrote in message ... I have a spreadsheet that our different locations used for scheduling. For each day of the week, the manager selects a start time and an end time. The total hours then calculates in the last column using a SUM formula. What has been requested is that an error pop up when that SUM forumula calculates hours that are greater than 40. I've tried every variation in data validation that I can think of but I can't get anything to work. Any suggestions welcome. I am working in Excel 2002. Thanks! |
Data Validation using whole numbers from formulas
I need to clarify a bit. For each day, when a start and end time is entered,
a third column in that day calculates the total hours for that day. The final total sums all the daily hours column to get the total hours. The final total is when I want to use the validation so that a pop up window shows when the total sum of hours is greater than 40. "brodiemac" wrote: I have a spreadsheet that our different locations used for scheduling. For each day of the week, the manager selects a start time and an end time. The total hours then calculates in the last column using a SUM formula. What has been requested is that an error pop up when that SUM forumula calculates hours that are greater than 40. I've tried every variation in data validation that I can think of but I can't get anything to work. Any suggestions welcome. I am working in Excel 2002. Thanks! |
All times are GMT +1. The time now is 12:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com