Skip to main content

I obviously don’t know what I’m talking about, so apologies in advance. I was inspired by this Team Schedule using Airtable blog post where they have a formula column that delivers “team member + Status” so I’m trying to create a formula that achieves something similar, but is a little more complex:


My columns are:

Brand = lookup from another table example “Client 1”

Project = lookup from another table example "Awesome Website"

Person = lookup from another table example “Betsy”

Status = single select example “Working” or “OOO”


My goal is to get the formula to deliver oShortened {Brand} name | {project}] - Status] or {person} - Status where the result EITHER shows:

shortened brand name + project + status (example C1 | Awesome Website - Working)

OR

person + status (example Betsy - OOO)


I’ve poked around several topics and the formula database trying to figure it out but I don’t know coding syntax or complex formulas. Here’s what I have so far:

Attempt #1 (kind of works?)


{Brand} &  " | " & AND({Project}, {Person}) & " - " & {Status}

Attempt #2 (doesn’t work)


IF(

{Brand}, SWITCH({Brand},

    "Client 1", "C1",

    "Client 2", "C2",

    "Client 3", "C3") & " | " & 

) & IF(

AND {Project},

{Person}), & " - " & {Status}

)

Attempt #3 (doesn’t work)


IF(

{Brand}, SWITCH({Brand},

    "Client 1", "C1",

    "Client 2", "C2",

    "Client 3", "C3") & " | " & 

) & IF(

OR(

FIND ("OOO", {Project},

{Person}), & " - " & {Status}

)

Any help you could provide would be much appreciated!

Hi Betsy. I haven’t actually tried my proposed solution below, so it might have unbalanced parens. The idea is simple, if you have {Brand} and {Project}, then construct the preferred string. If not, but you do have {Person}, then construct the secondary string. I didn’t put checks in for {Status} but you can if you want. Good luck.



IF(AND({Brand},{Project}), {Brand} & ’ | ’ & {Project} & ’ - ’ & {Status}, IF({Person}, {Person} & ’ - ’ & {Status}))


I’d love to take full credit for this but I had IRL help to get the formula to work. Nevertheless the solution:


IF(

    {Brand} = BLANK(),

    {Person} & " - " & {Status},

    {Brand}  & " - " & {Project} & IF({Status} = BLANK(), "", " - " & {Status})

)

(attaching a picture also of how the setup was explained to me)


It also seems to work as simply as this (assumes brand and project operate as a pair in that they are both full or both empty


{Brand} & " " & {Project} & {Person} & " - " & {Status}

thanks for the help @augmented I appreciate the response

Reply


Terms & Conditions