Help

Re: Formula novice IF formulas that choose between two columns

Solved
Jump to Solution
570 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Betsy_DeWitt
5 - Automation Enthusiast
5 - Automation Enthusiast

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 [Shortened {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!

1 Solution

Accepted Solutions
Betsy_DeWitt
5 - Automation Enthusiast
5 - Automation Enthusiast

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)
Screen Shot 2021-08-04 at 1.48.19 PM

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

See Solution in Thread

2 Replies 2
augmented
10 - Mercury
10 - Mercury

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}))

Betsy_DeWitt
5 - Automation Enthusiast
5 - Automation Enthusiast

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)
Screen Shot 2021-08-04 at 1.48.19 PM

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