Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Conditional Formatting with Drop Down Menus
My problem is as follows -
I am creating a spreadsheet to monitor response times to incoming incidents to my department. There are three different types of incidents - Critical, Major and Minor. Each of these have different times that we should respond in. Critial : 30 Minutes Major : 1 Hour Minor : 24 Hours When entering information into the spreadsheet I have a drop down menu so the user can choose Critical, Major or Minor. I have a simple formula that if you enter the incoming date and time and the response date and time it works out the time difference. I have also used conditional formating to highlight fields in red that take longer than 24 hours. Is there any way to set values for each of these items on the drop down menu. For example if the user chooses Critical and the response time is worked out to be over 30 minutes it highlights the field in red. But if the user chooses Major it only highlights the field if the response time is over 1 hour. Many Thanks, Chris |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Conditional Formatting with Drop Down Menus
You can do that with conditional formatting and a formula like
=OR(AND($B2="Critical",$C2TIME(0,30,0)),AND($B2=" Major",$C2TIME(1,0,0))) for example -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... My problem is as follows - I am creating a spreadsheet to monitor response times to incoming incidents to my department. There are three different types of incidents - Critical, Major and Minor. Each of these have different times that we should respond in. Critial : 30 Minutes Major : 1 Hour Minor : 24 Hours When entering information into the spreadsheet I have a drop down menu so the user can choose Critical, Major or Minor. I have a simple formula that if you enter the incoming date and time and the response date and time it works out the time difference. I have also used conditional formating to highlight fields in red that take longer than 24 hours. Is there any way to set values for each of these items on the drop down menu. For example if the user chooses Critical and the response time is worked out to be over 30 minutes it highlights the field in red. But if the user chooses Major it only highlights the field if the response time is over 1 hour. Many Thanks, Chris |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Conditional Formatting with Drop Down Menus
On Mar 3, 3:29 pm, "Bob Phillips" wrote:
You can do that with conditional formatting and a formula like =OR(AND($B2="Critical",$C2TIME(0,30,0)),AND($B2=" Major",$C2TIME(1,0,0))) for example -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... My problem is as follows - I am creating a spreadsheet to monitor response times to incoming incidents to my department. There are three different types of incidents - Critical, Major and Minor. Each of these have different times that we should respond in. Critial : 30 Minutes Major : 1 Hour Minor : 24 Hours When entering information into the spreadsheet I have a drop down menu so the user can choose Critical, Major or Minor. I have a simple formula that if you enter the incoming date and time and the response date and time it works out the time difference. I have also used conditional formating to highlight fields in red that take longer than 24 hours. Is there any way to set values for each of these items on the drop down menu. For example if the user chooses Critical and the response time is worked out to be over 30 minutes it highlights the field in red. But if the user chooses Major it only highlights the field if the response time is over 1 hour. Many Thanks, Chris Thank you Bob, that has worked a treat. I am now trying something extra in regards to dates, exactly the same concept as the first problem with time but it is in regards to dates. For example Critical need to be solved in 1 Day, Major in 14 and Minor in a month. Can I use the same conditional format concept using DATE like below - =OR(AND($B2="Critical",$C2DATE(??)),AND($B2="Majo r",$C2DATE(??))) Could I ask what parameters I would need to validate the dates? Thanks, Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop Down Value to Trigger Conditional Formatting | Excel Worksheet Functions | |||
Creating Drop-down menus with subset drop-down menus | Excel Worksheet Functions | |||
Conditional Formatting a drop down list | Excel Worksheet Functions | |||
Drop Down Menu Conditional Formatting | Excel Discussion (Misc queries) | |||
Formatting (conditional?) a drop down box selection | Excel Discussion (Misc queries) |