Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Data Validation
I have a comments field on the spreadsheet that I'm working on, which must be
completed if the value in another field = "N". If the value is anything other than "N" (including blank) a comment is not mandatory. I've wandered around the forums looking for something similar and can't find anything. In the same spreadsheet, I also need to validate that the time is entered as HH:MM in 24hr clock. All help is greatly appreciated! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Data Validation
If E1 is where the N is/is not and G1 is where the comment should go: click
on G1 and use Format | Conditional Formatting. In the dialog box set to Formula Is and use =AND($E$1="N",ISBLANK($G$1)); set up some formatting such as a red pattern. Now G1 will have red background when E1 is "N" and G1 is empty. To prevent someone just typing a single quote you could use a formula such as =AND($E$1="N",LEN($G$1)<6) If I type '7:00' Excel assume I mean 7 AM. If I type '7:00 PM' but format the cell h:mm then the cell displays 19:00 while the Formula Box display 7:00 PM. Any help? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "MuppetBaby" wrote in message ... I have a comments field on the spreadsheet that I'm working on, which must be completed if the value in another field = "N". If the value is anything other than "N" (including blank) a comment is not mandatory. I've wandered around the forums looking for something similar and can't find anything. In the same spreadsheet, I also need to validate that the time is entered as HH:MM in 24hr clock. All help is greatly appreciated! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Data Validation
Thanks Bernard, I hadn't thought of using conditional formatting. I was
hoping to use data validation and show the circles as I already have 80% of my sheet set up this way and don't want to confuse the users. As far as the time format goes it's more the use of : that I'm trying to enforce as I have to calculate the number of hours between A and B and if the user enters 7.00 or 7-00 or even 7 it won't work. Jude "Bernard Liengme" wrote: If E1 is where the N is/is not and G1 is where the comment should go: click on G1 and use Format | Conditional Formatting. In the dialog box set to Formula Is and use =AND($E$1="N",ISBLANK($G$1)); set up some formatting such as a red pattern. Now G1 will have red background when E1 is "N" and G1 is empty. To prevent someone just typing a single quote you could use a formula such as =AND($E$1="N",LEN($G$1)<6) If I type '7:00' Excel assume I mean 7 AM. If I type '7:00 PM' but format the cell h:mm then the cell displays 19:00 while the Formula Box display 7:00 PM. Any help? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "MuppetBaby" wrote in message ... I have a comments field on the spreadsheet that I'm working on, which must be completed if the value in another field = "N". If the value is anything other than "N" (including blank) a comment is not mandatory. I've wandered around the forums looking for something similar and can't find anything. In the same spreadsheet, I also need to validate that the time is entered as HH:MM in 24hr clock. All help is greatly appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
custom data validation | Excel Discussion (Misc queries) | |||
Custom Data Validation | Excel Discussion (Misc queries) | |||
Custom Data Validation | Excel Discussion (Misc queries) | |||
Custom data validation | Excel Discussion (Misc queries) | |||
Data Validation - Custom | Excel Discussion (Misc queries) |