Data validation - require nearby cell not be blank

Question

I'm trying to create a "comments" field in a worksheet for error-checkers that can only be used when certain errors are flagged as present. (by writing a whole number in the relevant cell)

I've tried a number of permutations but they all give an error no matter what state the referenced cells are in.

I've tried AND(S3<>"",W3<>""), COUNT(S3,W3)>0, COUNTBLANK(S3,W3)=0, and AND(NOT(ISBLANK(S3)),NOT(ISBLANK(W3))) as custom data validation, with and without "Ignore blanks" checked.

Am I missing something? Is this not possible without VBA? (Ideally I don't want to have to make my collegues click on macros every week)

Solution 1

Pending clarification of requirement, the following might suit:

=NOT(AND(ISBLANK(S3),ISBLANK(W3)))

with Ignore blank UNchecked.

Solution 2

I managed to make something similar to this work - I had to show an error message if 3 specific cells were left blank. Here's the validation I used (with ignore blanks unchecked):

=NOT(OR(ISBLANK(A2),ISBLANK(J1),ISBLANK(J2)))

That is, if either cell A2, cell J1 or cell J2 are blank, then

OR(ISBLANK(A2),ISBLANK(J1),ISBLANK(J2))

returns TRUE, and, therefore,

NOT(OR(ISBLANK(A2),ISBLANK(J1),ISBLANK(J2)))

returns FALSE, which triggers the warning message.

Hope this helps!

Comments

[K.A.Monica]

To make sure I understand, you want a formula that disallows editing of a certain cell if there is no content in a certain, different cell?

[user190280]

I want a custom data validation check that allows me to restrict a user from editing the comments field if there isn't a value in nearby cells. (So the formula should evaluate to "true" or "false") The issue I seem to be getting is that the data validation ALWAYS evaluates to false even when it blatantly shouldn't. EDIT: I still have this issue with IF(OR(NOT(ISBLANK(S3)),NOT(ISBLANK(W3)),1,0), too.

[Shekhar]

@pnuts I wasn't aware of it, thanks for pointing it out :)

[Nick Perkins]

@Pnuts yeah not 100% sure but thought it might be right. And if there are a pair of cells, then yes the other answer is much better :)

[Peter Albert]

Nick, just a side remark: Instead of =IF(E8="",FALSE,TRUE), you can simply write =E8<>"" ;-)

[user190280]

This looks like it should be the correct solution but I'm still having problems with it. I imagine this means the reason the other solutions also aren't working are some other problem related to the sheet then. Thanks for your help anyway! I'll just go with my backup plan of using the input message to discourage unnecessary comments. ;)

[blackworx]

You can ensure clearing the validated cell is always permitted (and keep "Ignore blanks" deselected as required) by specifically allowing an empty string in the validation formula. e.g. if the validated cell address in your example was F8, then the validation formula would be =OR(F8="",E8<>"")