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!

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!

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!