Using IF(ISBLANK) formula returns a circular error

Options

I have two columns, both Contact List but not enforced. Requestor Name comes in with a new request from a form and can be an email or contact. Project Owner is not updated for these entries. We also enter rows in different ways, and will assign a Requestor Name from contact list.

I'd like to create a formula which copies Requestor Name to Project Owner but only when Project Owner is blank.

AI generated formula: =IF(ISBLANK([Project Owner]@row), [Requestor Name]@row, [Project Owner]@row).

When I put the formula in the Project Owner cell, it returns a #circular error.
If I create a new column (Test) and put the formula in that cell, the formula works, but the Requestor Name is now copied into the Test cell.

I cannot seem to get the formula to work in the Project Owner column. Thanks for your help.

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭
    edited 05/14/24
    Options

    a circular error is basically saying that your formula is relying on another cell with a formula that relies on the cell your trying to enter into. In this case you have a formula referring to the very cell the formula is in.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Mark.poole
    Mark.poole ✭✭✭✭✭
    Options

    a work around is to create a second sheet that rows are copied to. Then reference the second sheet for your formula.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Cathy Salscheider
    Options

    Thanks Mark. I understood that the error was because the formula is referring back to itself. So much for the AI formula. :) The idea of making another sheet unfortunately seems like overkill.

    I can do a very simple formula =[Requestor Name]@row and manually assign it to each row where there is no value in [Project Owner]. But I was hoping to automate it for new entries so I don't need to keep an eye on it.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!