Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Using Approvals with a formula cell

I am trying to use an approval request to change the value of a cell that contains a formula.  The cell that has the formula is based on data in two other cells.  For example, the other two cells can either be "Yes" or "N/A".  If both of them are "N/A", then my approval cell automatically changes to "Yes" and it does not generate an approval request.  If either or both of the cells are "Yes", then it changes to "Submitted" and sends an approval request.  The problem is my approval request does not overwrite the formula, so the cell does not change.  Is there any way to get around this?

Thanks, Kyle

Comments

  • Schiff A.
    Schiff A. Employee

    Hi Kyle,

    Here's a workaround you can use.  Essentially, this solution uses an additional column to store approval status and updates the formula to display the contents of the approval status column .

    1. Create a new approval status column called "Approval"

    2. In your formula column, enter =IF(ISBLANK(Approval1), IF(AND([Column1]1 = "No", [Column2]1 = "No"), "Yes", "Submit"), Approval1)

    Be sure to update "Column1" and "Column2" to reflect the names of your columns. This formula as written will only proceed if the approval column is currently blank. Once the Approval column is not blank, the cell will display the contents of the Approval column which would include options for Submitted, Approved, and Denied as specified.

    3. Set up your Approval request to trigger when the formula cell displays "Submit"

     

    Please don't hesitate to reach out if you need further assistance.

    Thanks,

    Schiff A.

    Smartsheet Technical Support

     

  • Hi, 

    As I know, after I set up the rule for approval request, they will send automatically and they can add the approval status on new column or using existing column. I usually try new column. So,  I choose old column, if it is not blank(e.g. have formula.,so smartsheet can not do that because formula inside? 

  • Peter, the approval request will still send, but because of the formula it will not update when it is marked "approved".  

  • I am trying to initiate an approval request based on a cell with a vlookup formula referencing another sheet. The vlookup cell is updating when the other sheet is updated, but the approval trigger is not sending the approval request email.

    If, however, I manually change the vlookup cell it sends the approval request email. I tried creating another column as suggested above with no success. Any ideas?

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

    Hi,

    Have it worked before?

    Have a fantastic weekend!

    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: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.

  • No, this is a fresh build. The logic works, but the automation doesn't. If I manually change the vlookup cell, it triggers the workflow.

  • I have had the same issue -- everything works except the automation using a formula.

This discussion has been closed.