Nested IF functions with ISBLANK...

Ben Misener

Hello SS community, I am building a sheet that has multiple layers of approvals. In one set, there could be 1 or 2 "Business Owners" required to approve a document. I have a workflow setup that changes the Business Owner Approval to "Requested" for both owners when it is kicked off. I have a formula built that sets drop-down symbols to various colour statuses based on the review status. What I have setup works correctly when there are two Business Owners but I will have scenarios when there is only one. I am stuck on the formal that will update the colour based on there being only one required approver, vs two.

Here is my current formula based on two owners:

=IF(AND([Business Owner 1 Approval]@row = "Requested", [Business Owner 2 Approval]@row = "Requested"), "Blue", 

IF(OR([Business Owner 1 Approval]@row = "Requested", [Business Owner 2 Approval]@row = "Requested"), "Yellow", 

IF(OR([Business Owner 1 Approval]@row = "Reviewed /w Comments", [Business Owner 2 Approval]@row = "Reviewed /w Comments"), "Yellow", 

IF(OR([Business Owner 1 Approval]@row = "Approved", [Business Owner 2 Approval]@row = "Approved"), "Green", "Red")))

This is as far as I have gotten to in my attempt to recognize only one Business Owner (#1) but I am struggling to have the same apply if Business Owner 2 is the only one populated. Logic would dictate that if there is only one Owner, it should only be in Business Owner 1 field but it's possible someone may have only Business Owner 2 populated.

=IF(NOT(ISBLANK([Business Owner 1]@row)), 

IF(AND([Business Owner 1 Approval]@row = "Requested", [Business Owner 2 Approval]@row = "Requested"), "Blue", 

IF(OR([Business Owner 1 Approval]@row = "Requested", [Business Owner 2 Approval]@row = "Requested"), "Yellow", 

IF(OR([Business Owner 1 Approval]@row = "Reviewed /w Comments", [Business Owner 2 Approval]@row = "Reviewed /w Comments"), "Yellow", 

IF(OR([Business Owner 1 Approval]@row = "Approved", [Business Owner 2 Approval]@row = "Approved"), "Green", "Red")))))

Please help!

Brett Wyrick

If you're not opposed to adding a new column to your sheet, I'd add a new column named "# of Business Owners" or similar.

Then I'd set that column to be a "column formula", something like:

=COUNT([Business Owner 1 Approval]@row, [Business Owner 2 Approval]@row)

Which would then tell you if you've only got one or two business owners immediately, right? Then you could run your first formula based on the value of that cell being either 1 or 2. Would that work?

Ben Misener

Hey Brett, than you very much! I think that might work! Is the ideal that I would modify my first statement to be something like, IF Owner count = 1, then.... and IF(OR) Owner 1 = 2, then...?

Brett Wyrick

@Ben Misener you got it. My initial thought with it is that would work around the potential of having a name in either "Business Owner" column.

But, after thinking about this, you'd still need to have lots of nested IF statements to get this to work. Not impossible, just lengthy. And now that I'm thinking of this a bit deeper you could actually remove the "Owner Count" column entirely. But the formula I wrote below includes the Owner Count column.

So something like...

=IF(AND([Owner Count]@row=1,OR([Business Owner 1 Approval]@row = "Requested",[Business Owner 2 Approval]@row = "Requested")),"Blue",

IF(AND([Owner Count]@row=1,OR([Business Owner 1 Approval]@row = "Reviewed /w Comments",[Business Owner 2 Approval]@row = "Reviewed /w Comments")),"Yellow",

IF(AND([Owner Count]@row=1,OR([Business Owner 1 Approval]@row = "Approved",[Business Owner 2 Approval]@row = "Approved")),"Green",

IF(AND([Owner Count]@row=2,[Business Owner 1 Approval]@row = "Requested", [Business Owner 2 Approval]@row = "Requested"), "Blue", 

IF(AND([Owner Count]@row=2,OR([Business Owner 1 Approval]@row = "Requested", [Business Owner 2 Approval]@row = "Requested")), "Yellow", 

IF(AND([Owner Count]@row=2,OR([Business Owner 1 Approval]@row = "Reviewed /w Comments", [Business Owner 2 Approval]@row = "Reviewed /w Comments")), "Yellow", 

IF(AND([Owner Count]@row=2,OR([Business Owner 1 Approval]@row = "Approved", [Business Owner 2 Approval]@row = "Approved"), "Green", "Red"))

I think that will work. Just need to try that out on your side; let me know if it works!

-------

If you wanted to remove the Owner Count column, you could replace all instances of "[Owner Count]@row" above with COUNT([Business Owner 1 Approval]@row, [Business Owner 2 Approval]@row)