Skip to main content

Hi all,


I’m currently struggling with a thorny Airtable formula issue. I think I need to be considering using a nested IF statement or a SWITCH formula, but i’m not 100% convinced that either will work. Here’s my problem statement…


I need to analyse the outputs of five columns, each of which is populated by a unique, formula-generated text value. Based on different combinations of these text values, I need to display a different summary sentence.


I thought that I could solve this issue by using the following nested IF statement…


IF({Biz Rules Provided Summary} = “Incomplete”,“Awaiting Business Rules”,

IF({Analysis Complete Summary}) = “Analysis Incomplete”, “Awaiting Analysis”,

IF({Screen Designed Summary}) = “Design Incomplete”,“Awaiting Screen Design”,

IF({Developed Summary}) = “Dev Incomplete”,“Development Incomplete”,

IF({Function Configured}) = “Config Incomplete”,“Awaiting Configuration”,“Function Configured”)))))


However, this formula doesn’t work. It seems to get stuck after the first IF statement and doesn’t process the 2nd, 3rd, 4th & 5th IF statements. This leaves the majority of the column empty.


Can anyone help me out and tell me where i’m going wrong? Should I be using AND’s in the formula somewhere?


Thanks all.


Mark

Try this!


IF({Biz Rules Provided Summary} = “Incomplete”,“Awaiting Business Rules”,

IF({Analysis Complete Summary}= “Analysis Incomplete”, “Awaiting Analysis”,

IF({Screen Designed Summary} = “Design Incomplete”,“Awaiting Screen Design”,

IF({Developed Summary}= “Dev Incomplete”,“Development Incomplete”,

IF({Function Configured} = “Config Incomplete”,“Awaiting Configuration”,“Function Configured”)))))

or try this…


IF({Biz Rules Provided Summary} = “Incomplete”,“Awaiting Business Rules”,

IF({Analysis Complete Summary}= “Analysis Incomplete”, “Awaiting Analysis”,

IF({Screen Designed Summary} = “Design Incomplete”,“Awaiting Screen Design”,

IF({Developed Summary}= “Dev Incomplete”,“Development Incomplete”,

IF({Function Configured} = “Config Incomplete”,“Awaiting Configuration”,0)))))

Hi @Steve_Vo.


Just tried both formulas…and sadly received this error both times “Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.”


Any ideas?

Hi @Steve_Vo.


Just tried both formulas…and sadly received this error both times “Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.”


Any ideas?


I don’t believe you can do a nested ifs statement for 5 different fields into one. You may have to do 5 of them separately.


example: 1. IF({Biz Rules Provided Summary} = “Incomplete”,“Awaiting Business Rules”)


example 2. IF({Analysis Complete Summary}= “Analysis Incomplete”, “Awaiting Analysis”)


try one on its own and see if it works?

I don’t believe you can do a nested ifs statement for 5 different fields into one. You may have to do 5 of them separately.


example: 1. IF({Biz Rules Provided Summary} = “Incomplete”,“Awaiting Business Rules”)


example 2. IF({Analysis Complete Summary}= “Analysis Incomplete”, “Awaiting Analysis”)


try one on its own and see if it works?


Hi Steve.


Thanks for your responses.


I could certainly make the IF statements separately - i’ve tried this and it works.


However, I need to write a formula that automatically updates other cells when the final one of these statements is true. Each of these statements are steps in a process…and each one supersedes the previous one. Maybe an IF statement isn’t what i’m looking for?

Hi Steve.


Thanks for your responses.


I could certainly make the IF statements separately - i’ve tried this and it works.


However, I need to write a formula that automatically updates other cells when the final one of these statements is true. Each of these statements are steps in a process…and each one supersedes the previous one. Maybe an IF statement isn’t what i’m looking for?


It sounds like you want some combination of all five results to appear in the field at once. Maybe this will do the trick. It puts a newline at the end of each result, so that each result appears on its own line.



Here’s the formula:


IF({Biz Rules Provided Summary} = "Incomplete","Awaiting Business Rules\n") &

IF({Analysis Complete Summary} = "Analysis Incomplete", "Awaiting Analysis\n") &

IF({Screen Designed Summary} = "Design Incomplete","Awaiting Screen Design\n") &

IF({Developed Summary} = "Dev Incomplete","Development Incomplete\n") &

IF({Function Configured} = "Config Incomplete","Awaiting Configuration","Function Configured")

It sounds like you want some combination of all five results to appear in the field at once. Maybe this will do the trick. It puts a newline at the end of each result, so that each result appears on its own line.



Here’s the formula:


IF({Biz Rules Provided Summary} = "Incomplete","Awaiting Business Rules\n") &

IF({Analysis Complete Summary} = "Analysis Incomplete", "Awaiting Analysis\n") &

IF({Screen Designed Summary} = "Design Incomplete","Awaiting Screen Design\n") &

IF({Developed Summary} = "Dev Incomplete","Development Incomplete\n") &

IF({Function Configured} = "Config Incomplete","Awaiting Configuration","Function Configured")

Hi @Justin_Barrett.


Cheers Justin. That worked really well.


Much appreciated.


Best,

Mark

Reply


Terms & Conditions