Data validation issues

[paul w]

Hi all, happy new year.

I cant seem to get my data validation to work.

In BD17 I enter my finish time after I’ve entered my job site (F17), travel (AG17) and start time (AN17), and if any of these cells are empty F17 or AG17 or AN17 then I need data validation to trigger an information message “Missing Data” , I can get it to work as a conditional format and highlight BD17, I can get it to work in a separate cell and it returns TRUE if any cells are empty and FALSE if all cells are completed. BD17 is the last cell to be completed, after the other 3

This is what I’ve tried to use for data validation and I get the message required, but only if all cells are completed (BD17) =AND(OR(F17="",AG17="",AN17=""),BD17>0)
I just cant seem to see where I'm going wrong, I've even tried nesting in an IF() and forcing a TRUE() or FALSE() but cant get it to work.
If I have to go down the conditional formatting road, is it possible to highlight or circle the cell with the missing data.

many thanks

Paul

 

[DanteAmor]

Try this

=AND(F17<>"",AG17<>"",AN17<>"",BD17<>"")

Ignore black = false:

 

[paul w]

G17<>"",AN17<>"",BD17

Many thanks for that,
I forgot all about the ignore blanks.
just need to work out how to have it do nothing if (F17) = "lunch", "sick", "holiday", or "bank holiday" I'm assuming I can nest this in an IF() statement.

again many thanks
Paul

 

[DanteAmor]

How about:

=OR(F17="lunch",F17="sick",F17="holiday",AND(F17<>"",AG17<>"",AN17<>"",BD17<>""))

 

[paul w]

How about:

=OR(F17="lunch",F17="sick",F17="holiday",AND(F17<>"",AG17<>"",AN17<>"",BD17<>""))

thanks again,
i had worked one out but yours is neater,

=IF(OR(F7="lunch",F7="sick",F7="holiday",F7="bank holiday"),TRUE,AND(F7<>"",AG7<>"",AN7<>"",BD7<>""))

paul

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.