How do I set several cell values to a specific value in a different column

Hi,

I am running into issue and I cannot think about a way to solve it.

I have a column (Drop-down list) containing specific statuses (Status Column) which I need to match to 5 different report statuses (Not Started, In Flight, On Hold, Cancelled, Completed).

The match is as follow:


I can match the "Not Started" and "In Flight" using the following formula =IF(Status@row="Not Started", Not Started", "In Flight" but I cannot match On Hold, Cancelled, Completed.

I tried to use =IF(OR or IF(AND but it did not work because I am querying always the same value in (Status@row)

I look forward reading your suggestions

Thank you in advance

Xavier..

Tags:

Best Answer

  • X M
    X M ✭✭✭✭
    Answer ✓

    @Andrée Starå I did not think about creating an automation to solve my problem. I just did and it provides the expected result. Thank you for the suggestion.

    @Bassam Khalil yes this is correct and I think creating a nested IF statement will be really challenging when the automation is faster and more flexible.

    Thank you both for your input, have a great day.

    Xavier..

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 08/09/21

    Hi @X M

    Hope you are fine, did you mean that you need a formula to check the text in Status column and populate the value as per your attached screenshot in Report Status column?

    for example

    if Status = Ongoing then Report Status=In Flight

    if Status = Hold for CMS then Report Status = On Hold

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @X M

    I hope you're well and safe!

    To add to Bassam's excellent advice/answer.

    You could use the change a cell workflow to change the status without a formula, so the values can be changed manually if needed.

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • X M
    X M ✭✭✭✭
    Answer ✓

    @Andrée Starå I did not think about creating an automation to solve my problem. I just did and it provides the expected result. Thank you for the suggestion.

    @Bassam Khalil yes this is correct and I think creating a nested IF statement will be really challenging when the automation is faster and more flexible.

    Thank you both for your input, have a great day.

    Xavier..

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!