Data Validation Rule

[Steve 1962]

Hi

In my table, I would like to set up a validation that does not allow a time entry to be inputted if it is earlier than the time noted above or beside it. For example -

For task "B", cell E3 completes its activity at 5:30 and task "C" commences its activity at 05:00 (cell D5) - this is a conflict as each subsequent row is a separate task that cannot commence before the prior one is completed.

Also cell D8 and cell E7 are in conflict - cell D8 cannot commence prior to the time in cell E7 (8:20). Also as you read right across each row, the times must be progressive. EG: cells C8 and B8 are in conflict. The "check" time in cell C8 has to be later than the time in cell B8 (6:55).

Just to complicate this a little more - there will be some rows missing as is the case in row 4.

Hoping someone can assist.

Steve


<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;text-decoration: underline;;">Arrive</td><td style="font-weight: bold;text-align: center;text-decoration: underline;;">Check</td><td style="font-weight: bold;text-align: center;text-decoration: underline;;">Commence</td><td style="font-weight: bold;text-align: center;text-decoration: underline;;">Complete</td><td style="font-weight: bold;text-align: center;text-decoration: underline;;">Depart</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">A</td><td style="text-align: center;;">03:00 0-Jan</td><td style="text-align: center;;">03:10 0-Jan</td><td style="text-align: center;;">03:20 0-Jan</td><td style="text-align: center;;">04:20 0-Jan</td><td style="text-align: center;;">04:30 0-Jan</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">B</td><td style="text-align: center;;">03:30 0-Jan</td><td style="text-align: center;;">03:40 0-Jan</td><td style="text-align: center;;">04:30 0-Jan</td><td style="text-align: center;background-color: #FFC000;;">05:30 0-Jan</td><td style="text-align: center;;">05:40 0-Jan</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">C</td><td style="text-align: center;;">04:40 0-Jan</td><td style="text-align: center;;">04:50 0-Jan</td><td style="text-align: center;background-color: #FFC000;;">05:00 0-Jan</td><td style="text-align: center;;">06:00 0-Jan</td><td style="text-align: center;;">06:10 0-Jan</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">D</td><td style="text-align: center;;">05:00 0-Jan</td><td style="text-align: center;;">05:10 0-Jan</td><td style="text-align: center;;">06:10 0-Jan</td><td style="text-align: center;;">07:10 0-Jan</td><td style="text-align: center;;">07:20 0-Jan</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">E</td><td style="text-align: center;;">05:30 0-Jan</td><td style="text-align: center;;">05:40 0-Jan</td><td style="text-align: center;;">07:20 0-Jan</td><td style="text-align: center;background-color: #FFC000;;">08:20 0-Jan</td><td style="text-align: center;;">08:30 0-Jan</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">F</td><td style="text-align: center;background-color: #FFC000;;">06:55 0-Jan</td><td style="text-align: center;background-color: #FFC000;;">06:45 0-Jan</td><td style="text-align: center;background-color: #FFC000;;">07:05 0-Jan</td><td style="text-align: center;;">08:05 0-Jan</td><td style="text-align: center;;">08:15 0-Jan</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

 

[Gerald Higgins]

This might be difficult, because the links between tasks look complicated.

As a general rule, you can use data validation to dis-allow entry of a time that is earlier than the time in another cell, like this (in Excel 2003)
Data
Validation
Allow, choose Time
Data, choose Greater than (or greater than or equal to, if you prefer)
Start time, enter the cell that you want to refer to, say A1.

If, for example, you wanted to do this by always referring to the cell immediately to the left, this would be straightforward, but because the dependencies look more complicated, this might be more difficult for you.

If you wanted to make it depend on two (or more) other cells, you could use the MAX function in the final box on the DV screen, like this

=max(a1,b1)

 

[Steve 1962]

Hi Gerald

Thanks for your response. Your first suggestion may help me out however, I am curious about your second answer which could assist me with something else.

Just unsure - what is the "final box on the DV screen" ?????

Hoping you can clarify. I assume this has something to do with VBA ?????

Steve

 

[Gerald Higgins]

what is the "final box on the DV screen" ?

No, nothing to do with VBA.

In the example I posted above, the final box on the Data Validation screen is "Start time" (if you've already selected the other options I specified).

 

[Steve 1962]

Thanks Gerald. You've been a great help.

Steve