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.

Circular Reference Issue

edited 12/09/19 in Archived 2016 Posts
10/04/16 Edited 12/09/19

So I have two fields I am trying to create a formula. The first field is Today's Date, in which it is autopopulated with the current date. I have a second field name Calendar End Date, I am trying to create the formula in this field.

 

What I am trying to do is create a check that if Today's Date is greater than the Calendar End Date then delete the contents of Calendar End Date. The Calendar End Date is already populated by another forumal that is drawing information from other fields, but regardless if I create a new sheet with just these two fields I cannot seem to get the formula to work without getting a circular reference error.

 

So in the Calendar End Date I have this formula.

 

=IF([Today's Date]2 > [Calendar End Date]2, "")

 

I know that the circular reference error comes from the fact that the formula references the field in which it is written. But I need to reference that field in order for the forumla to work as intended. I also cannot create another field to accomplish this due to the purposes of the field and the current formula in it.

 

Any possible way to accomplish this?

Tags:

Comments

  • Scratch this question, I was able to solve it.

  • @Shawn R How were you able to solve your problem? Please share your final formula with us. Thanks!

  • @QueenD This is my Final Formula.

     

    =IF([Return Date]2 < [Today's Date]2, Delete2, IF([Approved?]2 = 1, IF([First Date Absent]2 < [Return Date]2, [Return Date]2 - 1, IF([First Date Absent]2 = [Return Date]2, [Return Date]2, ""))))

    I created the delete field as a permanent empty field and referenced it to populate the field the formula is written in. This allows me to delete the contents of the Calendar End Date column without the circular reference issue.

  • Thank you Shawn!

This discussion has been closed.