Update a cell in a column with data from a different columns

vivekr
vivekr
edited 12/09/19 in Formulas and Functions

Hi,

I'm trying to select a range for the completion status and also trying to refer to a different column and get the data updated in a different column. I'm sure I'm doing something wrong here. Please help me!

=IF(COUNTIF([Completion Status]3:[Completion Status]38, 0) > 0.5, "Not Completed", "Pending Mgmt Approval", IF([CEO Approval]3 = "Approved", "Completed", "In Progress"))

 

Here's a video screen share of the issue for further clarification:

https://www.loom.com/share/97f0ac40869e4f73992cc4f77ca2ac1a

 

Thanks

Comments

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

    Hi Vivek,

    Try something like this.

    Change the row numbers to match your sheet. I haven't included the In Progress status because I wasn't sure what criteria would decide if it's in progress. What would you lie to use for In progress? When over one checkbox is checked?

    =IF([CEO Approval]@row = "Approved"; "Completed"; IF(COUNTIF([Completion Status]4:[Completion Status]8; 0) > 0,5; "Not Completed"; "Pending Mgmt Approval"))

    The same version but with the below changes for your and others convenience.

    =IF([CEO Approval]@row = "Approved", "Completed", IF(COUNTIF([Completion Status]4:[Completion Status]8, 0) > 0.5, "Not Completed", "Pending Mgmt Approval"))

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did it work?

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!