Data Validation multiple restrictions in one cell

Try
=AND(B9<>0 ,C9<>0)

Cannot identify which cell fails in error message but you could put formula below in adjacent cell
=IF(B9=0,"B9 needs you",IF(C9=0,"C9 needs you",""))

Replace the formula with this:
=AND(B9<>0,C9<>0)
Make sure 'Ignore blank' is not ticked.

I don't know of any way to have the Error Alert change depending on which cell(s) are not filled in, sorry. It may be possible with VBA, but that's not my strong point.

Okay, I've had a bit of a play and come up with a bit of a cheat to display which cell(s) are blank.

1. Insert a new column to the left of column E (in other words, a new column E so that your E9 becomes F9).
2. In the new E9, type this formula:

Formula:

Please Login or Register  to view this content.

3. Set the width of the new column E as 0.08 (that's the smallest non-zero width Excel will accept).
4. Adjust the width of column F (the old column E) so it's wide enough to show the whole error message if necessary.

If you want to be able to drag this down, you'll need to replace the hard-coded B9 and C9 with generated cell addresses. You can do this with CELL, like this:

Formula:

Please Login or Register  to view this content.

The attached file shows the above working.

Hope that helps.

Another alternative would be to use conditional formatting on cell E9

User should only try to input if cell contains no colour
- image below is DataValidation error message which explains the colour

DV with CF.jpg

In any case the cells are very close to E9 - the user could simply look at both cells after seeing your warning!

Glad we could help and thanks for the rep.

If that takes care of your original question, then please take a moment to mark the thread as Solved so others know there's an answer here (instructions are in my sig and in kev's message above). Thanks.