Data Validation

[paul w]

hi all,

busy trying to work out some data validation on my time sheet, to stop erroneous entry's.

column A is travel time start
column B is job start time
column C is job stop time.

I need to check that the start time is the same as the previous stop time, eg
job 1 travel 0800 start 0820 stop 0900
job 2 travel 0900 start 0915 stop 0930

I use this formula in column A cells (data validation, custom, ignore blanks) =A4=c3 and this works great, if the data doesn't match then I get a stop error.

the problem I'm having is I need a formula to check column B (job start time "B4", against previous stop time "C3") but only if there is no data in the travel time cell "A4"

eg. check B4 matches C3, if A4 is empty,(if it doesn't then stop error) but if A4 is used then ignore the data validation process. hope this makes sense.

many thanks

 

[JustynaMK]

Hi Paul,

For cell B4, try using the following formula:
=IF(LEN(A4),TRUE,B4=C3)
(data validation, custom, do NOT ignore blanks)

Let me know if it works for you.

 

[paul w]

Hi, thanks for your reply,
this works great if there isn't a blank cell, (I copied validation down "B" column)
the problem I have now is that I start a new day after a blank row, so the validation fails because eg, cell B8 has data but cell C7 is blank.
is there a way of making this work but ignore blanks.

many thanks

Paul

 

[JustynaMK]

Hi Paul,

Maybe something like that for cell A8:
=A8=IF(LEN(C7),C7,C6)
and for B8:
=IF(LEN(A8),TRUE,B8=IF(LEN(C7),C7,C6))
Both Data Validation rules should not ignore blanks.

 

[paul w]

sorry I've been away from the computer,
thanks for your formula, they again work great, but fail if validating against a blank cell,
I've attached a drob box link to the sheet,
I've removed the validation in the yellow section, so you can see what the sheet should look like, the green section has your validation formula, if there is a blank cell in the stop column, the validation fails. the sheet has been unprotected, so you can use it.
again your help is much appreciated.

you can see why I need the times to be correct, as the totals will be wrong.

many thanks
paul

https://www.dropbox.com/s/hgu1iah4szrmbde/test data validation.xlsx?dl=0

 

[JustynaMK]

Hi Paul,

Thanks for sharing the file. I noticed that your Data Validation for "Start" column is referring to incorrect row numbers, for example in cell AY24 you have:
=IF(LEN(AS24),TRUE,AY24=IF(LEN(BD24),BD24,BD23))
It should be adjusted to:
=IF(LEN(AS24),TRUE,AY24=IF(LEN(BD23),BD23,BD22))

The same for the first Data Validation ("Travel Between"), cell AS24:
=AS24=IF(LEN(BD24),BD24,BD23)
It should be changed to:
=AS24=IF(LEN(BD23),BD23,BD22)

You also need to change all your other Data Validations accordingly.
Please let me know if it solves the issue.

 

[paul w]

thanks again for you reply, unfortunately it is still failing,
I copy and pasted the formula, then copied across the required cells,
my sheet starts on row 7, so when I enter a time into "travel from or "Start" cell it fails because it is looking for a match with the cell that says "Stop:"

it also happens further down the sheet eg. if I have a stop time on row 14 then start a new day on row 16 it is looking for a match to that cell on row 14,

hope that made sense

many thanks

 

[paul w]

Solved.

just like to say a big thankyou to JustynaMK for your help with this problem I was having, and the formula you provided pointed me in the right direction,

with a bit of trial and error this is what I ended up with, and it seems to work.

data validation
in cell AS7 (travel start) =AS7=BD6 (ignore blanks)
in cell AY7 (Start) =IF(AS7>0,TRUE,IF(OR(AY7=BD6,BD6=""),TRUE,FALSE)) (Do not ignore blanks)

there is probably a shorter way of writing these, but they do what I need, and they work,
once again many thanks.

Paul

 

[JustynaMK]

Thank you Paul for confirming and apologize I wasn't able to come back to you earlier! Glad everything works fine. Take care.

 

[paul w]

No apologies necessary,
I appreciate you help with this, I'm no expert just a little self taught and tweaking formula and of course from people like yourself who are willing to share their knowledge and time, over the years from different time sheets I've used,
I've learnt a lot from you as regards to the LEN function and true value result, that's why I tried the IF OR statement with true ,false.
I tried you formula on a blank worksheet and they both work, its just when I used them on the real sheet that they fail, don't know why, it could be the custom formatting, (to insert a colon) hence the reason for the very funny looking formulas to calculate the time used.
It wont stop fat finger entry's but should be picked up by the validation, or a strange result in the total, before you get too far into the sheet, as by the end of the week there may be 60 entry's over 3 pages.


many thank

paul