0

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)

  • 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? – K.A.Monica Jan 16 '13 at 0:08
  • 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. – user190280 Jan 16 '13 at 0:25
  • You could do it with VBA without people needing to click on macros. Use an worksheet_change event to monitor if a comment cell is modified and then check if the associated data field is blank or not. If they are all in a standard layout (data in column 3, comment column 5) then it is pretty simple. – Nick Perkins Jan 16 '13 at 1:10
  • 1
    @pnuts I wasn't aware of it, thanks for pointing it out :) – Shekhar May 2 '13 at 14:29
1

Pending clarification of requirement, the following might suit:

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

with Ignore blank UNchecked.

SU536862 example

  • 1
    Looks good and similar to my answer which I posted later. Yours wasn't up at the time. :) – Nick Perkins Jan 16 '13 at 1:15
  • 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. ;) – user190280 Jan 16 '13 at 19:07
1

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!

0

I found that this worked for me

Screenshot of Validation dialog

The issue is that if E8 is made blank you then can't clear anything from the validated cell either. I couldn't get it to work with "Ignore blank" though.

  • @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 :) – Nick Perkins Jan 16 '13 at 2:59
  • 2
    Nick, just a side remark: Instead of =IF(E8="",FALSE,TRUE), you can simply write =E8<>"" ;-) – Peter Albert Jan 16 '13 at 7:30
  • 1
    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<>"") – blackworx Jan 19 '17 at 14:05
-1

A solution that appears to be effective and which is also simple can be found at:

https://trumpexcel.com/conditional-data-entry-in-excel/

Basically, it involves disabling "Ignore Blanks and then specifying that the reference cell be non-blank. For example, if any cell in column C should accept input only if there is a value in column B, the validation formula for cells in column C might be =$B1<>""

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.