Formula with multiple checkboxes and each has an associated comment

Bill Price

I have a form with multiple departments that are checked if they are affected. sometimes it might be one, sometimes it might be all (12 of them) or anywhere in between. If they are checked an update request is sent and they fill out a comment. I need another column (called Fully Reviewed) to be "No" until all departments with a check have filled out a comment and then it will be "Yes". I have tried multiple formulas. I get "Yes" when all are checked but only some comments are filled or "No" no matter what the situation. or I get UNPARESABLE or INVALID COLUMN VALUE.

This is my latest formula that returns Yes before all comments associated with checked columns are filled.

=IF(OR(AND([Legal]@row = 1, NOT(ISBLANK([Legal Comments]@row))), AND([OMB]@row = 1, NOT(ISBLANK([OMB Comments]@row))), AND([CapitalProjects]@row = 1, NOT(ISBLANK([CP Comments]@row))), AND([Finance]@row = 1, NOT(ISBLANK([Finance Comments]@row))), AND([Accounting]@row = 1, NOT(ISBLANK([Accounting Comments]@row))), AND([Purchasing]@row = 1, NOT(ISBLANK([Purchasing Comments]@row))), AND([UtilityServices]@row = 1, NOT(ISBLANK([Utility Comments]@row))), AND([Revenue]@row = 1, NOT(ISBLANK([Revenue Comments]@row))), AND([HR]@row = 1, NOT(ISBLANK([HR Comments]@row))), AND([ITS]@row = 1, NOT(ISBLANK([ITS Comments]@row))), AND([RealEstate]@row = 1, NOT(ISBLANK([RE Comments]@row))), AND([ChiefsOffice]@row = 1, NOT(ISBLANK([CO Comments]@row)))), "Yes", "No")

prime_nathaniel

@Bill Price

Is there a reason you can't greatly simplify the approach but just doing a count of checked boxes then a count of comments like in this example below?

Then you just compare that countNumber = countComment, if its true then it's all reviewed

prime_nathaniel

@Bill Price

Is there a reason you can't greatly simplify the approach but just doing a count of checked boxes then a count of comments like in this example below?

Then you just compare that countNumber = countComment, if its true then it's all reviewed

Bill Price

Thanks, I'll give that a try. I inherited this sheet and was trying to make as few changes as possible until this broke. I figured there was an easier way.