Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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)

share|improve this question
    
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 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 :) –  Shakehar May 2 '13 at 14:29
add comment

2 Answers

Pending clarification of requirement, the following might suit:

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

with Ignore blank UNchecked.

SU536862 example

share|improve this answer
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
    
@NickPerkins I've known postings at identical times - can cause problems! Even close can be an issue (though a specific example I was thinking of seems to have disappeared). Gracious of you. –  pnuts Jan 16 '13 at 1:45
    
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
    
@user190280 Some at least of what you tried looks to me as though it would not have worked. Obviously it is up to you when to ‘call time’ but if the above is what you would like then I think we have done the difficult part and I can assure you the result is achievable along the lines you tried. If you wanted to ‘see it through’ you might for example check your test cells are truly blank and not, for example, occupied by one or more spaces, or a formula that outputs "". –  pnuts Jan 16 '13 at 23:36
add comment

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.

share|improve this answer
    
TQ. But are you sure you know what the OP wants?? (that's not being facetious, I'm not!) Also (aside!) I think there are a pair of trigger cells, S3 and W3, not just E8. –  pnuts Jan 16 '13 at 1:19
    
@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
    
Nick, just a side remark: Instead of =IF(E8="",FALSE,TRUE), you can simply write =E8<>"" ;-) –  Peter Albert Jan 16 '13 at 7:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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