IF certain columns are not blank then autofill another column based on this?

Options

Hi, I want to autofill a column [Column 1] based on whether a number of other columns contain text/date, i.e.


IF [Column 2] is not blank then put '-' in [Column 1]

IF [Column 3] is not blank then put 'P1' in [Column 1] (this would override above)

IF [Column 2] is not blank then put 'P2' in [Column 1] (this would override above)

IF [Column 2] is not blank then put 'P3' in [Column 1] (this would override above)


The function can be contains text, a date or is not blank. It doesn't really matter. Would appreciate any help!

Tags:

Best Answer

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Emmet McKenna

    Try something like this.


    =IF([3]@row <> ""; "P3"; IF([2]@row <> ""; "P2"; IF([1]@row <> ""; "P1")))

    The same version but with the below changes for convenience.

    =IF([3]@row <> "", "P3", IF([2]@row <> "", "P2", IF([1]@row <> "", "P1")))

    Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.


    Did that work?

    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 help the Community by marking it as the accepted answer/helpful. 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Emmet McKenna

    Try something like this.


    =IF([3]@row <> ""; "P3"; IF([2]@row <> ""; "P2"; IF([1]@row <> ""; "P1")))

    The same version but with the below changes for convenience.

    =IF([3]@row <> "", "P3", IF([2]@row <> "", "P2", IF([1]@row <> "", "P1")))

    Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.


    Did that work?

    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 help the Community by marking it as the accepted answer/helpful. 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

  • Emmet McKenna
    Emmet McKenna ✭✭✭✭
    Options

    Thanks Andrée, that worked perfectly!!!

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

    @Emmet McKenna

    Excellent!

    You're more than welcome!

    Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

  • Khanambano
    Options

    Hi,


    I was wondering if you can assist me with a similar IF formula. I am trying to automate the status column for completed work based on "All Work Completed Date." The formula im trying to incorporate should say IF there is a date in the "all work completed column, then the status should change to complete.

    =IF([All Work Complete Date@row] <>""; "Complete) is giving me unperishable. know how i can fix this?




  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Khanambano

    It looks like you're just missing an end quote around the word "Complete" or "Completed"

    =IF([All Work Complete Date@row] <>""; "Complete")

    However, I would recommend using a Change Cell workflow instead of a formula in this instance! That way you can keep other manually entered values (such as "In Progress") in the column, but the automation will change it to "Complete" when a date is entered.

    Here's more information: Change the Value of a Cell in an Automated Workflow

    Cheers,

    Genevieve

  • Khanambano
    Options

    Hi @Genevieve P.

    I tried to the automated workflow and i made a major mistake which changed the orginal data set that i had. I put in a customer support request with smartsheet to restore my previous sheet.

    This is what my automation looks like:


    However, when i turned this automation on, it changed all rows, even those that were blank to completed.


    How can i fix this? your help is appreciated immensely as i just messed up my entire data set.

  • Khanambano
    Options

    Hi @Genevieve P.

    I tried to the automated workflow and i made a major mistake which changed the orginal data set that i had. I put in a customer support request with smartsheet to restore my previous sheet.

    This is what my automation looks like:


    However, when i turned this automation on, it changed all rows, even those that were blank to completed.


    How can i fix this? your help is appreciated immensely as i just messed up my entire data set ='(

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Khanambano,

    What plan type are you on? If you're on a Business or Enterprise plan and you're either an Admin or Owner of the sheet, you can access the Activity Log in the sheet to download a Sheet Snapshot: Request a sheet snapshot

    This will give you an excel file of the data so you can copy/paste in the correct Status back into the column.

    In regards to your workflow, I would suggest adding in a Condition Block to make sure the status is only updated if the change to the cell is not-blank.

    Let me know if that worked!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!