Using Approvals with a formula cell

Kyle Kaldor

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

Schiff A.

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

 

Peter Smith

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? 

Kyle Kaldor

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

radkison

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å

Hi,

Have it worked before?

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

radkison

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.

Kristin Komassa

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