How to set a cell value based on another column. If false do nothing to original value

Adam Wilson
Adam Wilson ✭✭✭
edited 12/09/19 in Formulas and Functions

I have a status column that is a dropdown list. I also have another date column "Goods and Services Received" that pulls a date using a VLOOKUP from another smartsheet. What I am trying to do is change the status column to "Complete" if there is a date in the "Goods and Services Received" column of the reference sheet, but leave the original value in the status column if there is no date in the "Goods and Services Column".



  • Adam Wilson
    Adam Wilson ✭✭✭

    The column Goods and Services Certification Received which is a column in this same sheet pulls a date from another sheet if there is a matching record. It will either have a date or indicate #NO MATCH. If there is a date in that column I want the status column changed to "Complete" If the column has #NO MATCH I want the Status column to display the original value in the Status column.

    I thought this might work but it always returns #CIRCULAR REFERENCE.

    =IF(ISDATE([Goods and Services Certification Received]1), "Complete", Status1)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    What is the original formula in the status column?

  • Adam Wilson
    Adam Wilson ✭✭✭
    edited 06/04/19

    The Status column is a dropdown list with the following options: I am trying to set the value to "Completed" only if a date is found in the column "Goods and Services Certification Received"

    Appro Creation

    Pending Approval

    Pending PO Creation

    Pending PO Approval

    Work in Progress

    Awaiting Certification


    Converting to Fixed

    Ready to Bill

    SJM Billed to Accounting

    Project Closed

    Ongoing Project

    Quote only Request

    Project Cancelled

    Project on Hold

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would have to automate all or none. Once a different selection is made manually it will override and delete the formula.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!