• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data validation with multiple criteria - 2 separate issues


New Member
Hi all,

I work for a company that tests engines on dynamometers. I have an Excel file that records the Utilisation of each test cell. So, we record what happens on each test bed over 24 hours each day and for 7 days each week. The file already has a data validation based system for recording a Main Category and then 3 more dependant sub-categories which works well, preventing previous categories from being changed without changing the last one.

Problem 1
What I would like to do is depending on which shift the user is entering data for (Shift 3) restrict the existing data validation (Sub Category) to a single option of '2950 - Open Capacity' if they select '2000 - Waiting' as the Main Category. The existing dependant data validation works with a named range called ValName referring to another sheet called Lookups in the attached file. I made this file over 7 years ago and I can't figure out how to make the change I want without messing up the existing data validation. I don't know if this would be easier to do with some code just to say for instance that if it's Shift 3 that's selected and the Main Category is '2000 - Waiting' then the Sub-Category has to be '2950 - Open Capacity' otherwise they get a message box saying it's not possible to enter a different Waiting Sub Category and it restricts the entry. The only other option that should be entered for Shift 3 is a Running Main Category (1000 - Running' where the engine will be running un-manned on an automated test which has it's own options as sub-categories. I did wonder with the coding option whether I could do a case statement perhaps to restrict the entry. Any thoughts on that appreciated. :)

Problem 2
I would also like to restrict the Hours column from Monday through to Thursday as 8 hours maximum on any row (8 hours maximum per shift so if they just select one category for their shift it can't be for longer than 8 hours) and then Friday the maximum should be max 14 hours on the 3rd shift and max 5 for shifts 1 and 2. For Saturday and Sunday the maximum can be 24 hours. If they select 24 hours on a weekend day that should also only be if the Waiting sub category is the '2950 - Open Capacity' has been selected. there's a lot of Ifs in that so again might be a case for some code perhaps.

I would welcome any thoughts on either issue. I have attached a working file with some proprietary bits removed



New Member
The Log sheet is the one where the users enter the data...the Lookups that I've just noticed the file opens on is for the existing data validation