Last week, someone asked me how to create drop down lists for hours and minutes, and also control the total time that was entered. Production time and Defect time would be entered, and Defect total time could not be greater than the Production total time.
He sent a sample file, with drop down lists in place, but they allowed invalid times to be entered. Was there any way to make it work?
It Can't Be Done
My first response was, "You can't do that with drop down lists. Just type the hours and minutes, and use a data validation formula to check the totals." I've used that technique in other workbooks, to compare totals, like the budget example, shown below.
The reply to my response was polite, but persistent, "Thank you, and if you can think of a way to use drop downs, please let me know."
Accepting the Challenge
That made me start thinking about ways to control the time entry drop downs. A cell can't combine drop down lists with custom data validation rules, but maybe I could create lists with only the valid numbers.
So, I tackled the challenge, and found a way to create dynamic named ranges with the valid numbers for each list – Production Hours, Defect Hours, Production Minutes and Defect Minutes. I used the OFFSET function for the Refers to formulas, but you could use the INDEX function if you prefer.
Limit the Drop Down Lists
Each drop down list is based on one of the dynamic named ranges, and they follow these rules:
- Production time must be equal to, or greater than, Defect time
- Defect time must be less than, or equal to, Production time
- Production time must be entered before Defect time can be entered (Defect drop downs do not work until Production time is entered
In the screen shot below, you can see the Defect Hours drop down. Production Hours has been set at 4 hours, so the Defect Hours drop down only shows the numbers from 1 to 4.
There are controls on the Production drop downs too, to prevent people from changing to an invalid time, after entering Defect times.
In the screen shot below, the Production Minutes has a minimum of 32, so it can't be set lower than the Defect time.
Calculate the Minimums and Maximums
There are worksheet formulas that calculate the minimum and maximum number for each list, and the number of items. You can see the formulas, and their explanations, in the sample file, and the written instructions.
Download the Sample File
To see how the drop down lists work, download the sample file from the Excel Data Validation - Select Hours and Minutes page on my Contextures website.