Skip to main content

Hello everyone

I'm Nawaf

I have a long nested if statement that i used to use in excel but I can't figure out how to use it in airtable 

the idea is i have 3 columns first ( permit status ) second is ( remark ) when the conditions meet, I want the third column ( action ) to show as "action" or "no action" or "no action closed"

(permit states) has only two terms closed or valid

(remark) is different i want to find the word "renewed" or "to be" or "re-issue" or "on hold" "redesign"

this is what i used to use in excel 

=IF(AND([@[Permit Status]]="Valid",[@Remark]="No need for renewal"),"no action",IF(AND([@[Permit Status]]="closed",[@Remark]="No need for renewal"),"no action closed",IF(AND([@[Permit Status]]="closed",[@Remark]=""),"need action",IF(AND([@[Permit Status]]="Valid",[@Remark]=""),"no action",IF(AND([@[Permit Status]]="Valid",[@Remark]="Renwd"),"no action",IF(AND([@[Permit Status]]="Valid",[@Remark]="Re-issued"),"no action",IF(AND(ISNUMBER(SEARCH("to be",[@Remark])),[@[Permit Status]]="valid"),"in progress",IF(AND(ISNUMBER(SEARCH("to be",[@Remark])),[@[Permit Status]]="closed"),"in progress",IF(AND(ISNUMBER(SEARCH("on hold",[@Remark])),[@[Permit Status]]="closed"),"no action",IF(AND([@[Permit Status]]="closed",[@Remark]="renewd"),"no action",IF(AND([@[Permit Status]]="closed",[@Remark]="Re-issued"),"no action",IF(AND([@[Permit Status]]="closed",[@Remark]="redesign"),"no action"))))))))))))

I would apricate any help 

Thank you

Airtable formulas use curly brackets to indicate whether we're referencing a field name.  With reference to your formula, if we're referencing a field called "[@Remark]", we would use "{[@Remark]}", and so you'd need to update your formula accordingly

I would also recommend formatting the formula so that you can view stuff easily

I've combined both of the changes I mentioned above below and you'd just need to make the changes accordingly:

IF(
AND(
{[@[Permit Status]]}="Valid",
{[@Remark]}="No need for renewal"
),
"no action",
IF(
AND(
{[@[Permit Status]]}="closed",
{[@Remark]}="No need for renewal"
),
"no action closed"
)
)


Link to base

Reply


Terms & Conditions