Sep 18 2019 08:17 AM
I'm having issues getting a calculated column to play nice. Basically in a list I have 7 "Approval" columns that are populated via a Approval Flows. I'm wanting this calculated column to update to "Approved" once the other 7 columns all switch to "Approve".
I either get a syntax message when I try to save the formula. Or the few iterations when it does accept it, it isn't behaving as intended. I'm sure I'm missing something obvious, but banging my head on the desk at this point.
Please help!
=IF([Column1]=Approve,AND([Column2]=Approve,AND([Column3]=Approve,AND([Column4]=Approve,AND([Column5]=Approve,AND([Column6]=Approve,AND([Column7]=Approve, "Approved","Pending")))))))
Sep 18 2019 09:15 AM - edited Sep 18 2019 09:16 AM
Solution@Jon Gertken It's very easy to get tangled up in quotes and missing parens. Plus, you can only next about 7 IF statements, so that makes it more difficult.
What I've done in the past is rethink it a bit to make the syntax a bit easier. I'd count how many of the fields are 'Approved' and then do the logic.
That part would look like:
=IF([column1]="Approved",1,0)+IF([column2]="Approved",1,0)+IF([column3]="Approved",1,0)+IF([column4]="Approved",1,0)+IF([column5]="Approved",1,0)+IF([column6]="Approved",1,0)+IF([column7]="Approved",1,0)
If a column is 'Approved', it adds one to the total, if not, it adds 0. This works because you are using the + for adding.
Then, wrap that whole thing in a IF(<codeFromAbove>=7,"Approved","Pending"), so the full thing would look like:
=IF(IF([column1]="Approved",1,0)+IF([column2]="Approved",1,0)+IF([column3]="Approved",1,0)+IF([column4]="Approved",1,0)+IF([column5]="Approved",1,0)+IF([column6]="Approved",1,0)+IF([column7]="Approved",1,0)=7,"Approved","Pending")
Sep 18 2019 09:32 AM
Thanks a million, saved me some headache. Had to tweak it a little but works like a charm.
Sep 18 2019 09:15 AM - edited Sep 18 2019 09:16 AM
Solution@Jon Gertken It's very easy to get tangled up in quotes and missing parens. Plus, you can only next about 7 IF statements, so that makes it more difficult.
What I've done in the past is rethink it a bit to make the syntax a bit easier. I'd count how many of the fields are 'Approved' and then do the logic.
That part would look like:
=IF([column1]="Approved",1,0)+IF([column2]="Approved",1,0)+IF([column3]="Approved",1,0)+IF([column4]="Approved",1,0)+IF([column5]="Approved",1,0)+IF([column6]="Approved",1,0)+IF([column7]="Approved",1,0)
If a column is 'Approved', it adds one to the total, if not, it adds 0. This works because you are using the + for adding.
Then, wrap that whole thing in a IF(<codeFromAbove>=7,"Approved","Pending"), so the full thing would look like:
=IF(IF([column1]="Approved",1,0)+IF([column2]="Approved",1,0)+IF([column3]="Approved",1,0)+IF([column4]="Approved",1,0)+IF([column5]="Approved",1,0)+IF([column6]="Approved",1,0)+IF([column7]="Approved",1,0)=7,"Approved","Pending")