Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
custom data validation
I have a sheet with 6 columns of dates (K to P). K is the job start date, P
is the job end date and the ones inbetween are inbetween dates. It is possible for some of the ones inbetween not to need to have a date in them (left blank). What I want to do is validate the cells so that the user cannot enter incorrect dates. For example, enter an end date which is before the start date. I have managed to validate just start date against end date but when it comes to a formula to include all the columns I get lost. PS The entry in the cell must be greater than 01/01/2007 and today |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
custom data validation
"Frank Pytel" wrote: Gareth; Have you considered Conditional Formatting. This will not prevent the user from entering information, but the code is a little lighter on your application. Select the Colum P cell(s) that you want alerted and Go To: Format Conditional Formatting. From the drop down menu select Formula and enter the following code: =if(K1P1,True,False) Click on the Format button and choose what you want to happen when this is true. The formula says that if the Start Date comes before the End Date, turn the "cell Red", "Font Green/Bold" or whatever else you desire. God Bless Frank Pytel Frank I do need to use data validation as cond formatting will let users enter the incorrect date even though they have been warned about it. Gareth |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
custom data validation
You don't need the IF(...,TRUE,FALSE) , Frank.
Instead of =if(K1P1,True,False) you can use =K1P1 as that will return a Boolean result. -- David Biddulph "Frank Pytel" wrote in message ... Gareth; Have you considered Conditional Formatting. This will not prevent the user from entering information, but the code is a little lighter on your application. Select the Colum P cell(s) that you want alerted and Go To: Format Conditional Formatting. From the drop down menu select Formula and enter the following code: =if(K1P1,True,False) Click on the Format button and choose what you want to happen when this is true. The formula says that if the Start Date comes before the End Date, turn the "cell Red", "Font Green/Bold" or whatever else you desire. God Bless Frank Pytel |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
custom data validation
Select L2:On and in DV use a formula of
=AND(L2$K2,L2<P2) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Gareth" wrote in message ... I have a sheet with 6 columns of dates (K to P). K is the job start date, P is the job end date and the ones inbetween are inbetween dates. It is possible for some of the ones inbetween not to need to have a date in them (left blank). What I want to do is validate the cells so that the user cannot enter incorrect dates. For example, enter an end date which is before the start date. I have managed to validate just start date against end date but when it comes to a formula to include all the columns I get lost. PS The entry in the cell must be greater than 01/01/2007 and today |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
custom data validation
"Bob Phillips" wrote: Select L2:On and in DV use a formula of =AND(L2$K2,L2<P2) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Gareth" wrote in message ... I have a sheet with 6 columns of dates (K to P). K is the job start date, P is the job end date and the ones inbetween are inbetween dates. It is possible for some of the ones inbetween not to need to have a date in them (left blank). What I want to do is validate the cells so that the user cannot enter incorrect dates. For example, enter an end date which is before the start date. I have managed to validate just start date against end date but when it comes to a formula to include all the columns I get lost. PS The entry in the cell must be greater than 01/01/2007 and today Bob I don't think that I have explained exactly what I need... All cells in the columns K:P need dv, each cell cannot be less than the cell to its left. For example: K2 cannot be greater than L2, M2, N2, O2 or P2 L2 cannot be less than K2 or greater than M2, N2, O2 or P2 M2 can be blank, cannot be less than K2 or L2 or greater than N2, O2 or P2 - etc M2, N2 and O2 can be blank. Dates entred must be =01/01/2007 Thanks in advance. Gareth |
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) |