Mark_Adams
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
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