Formula to blank out a cell

Options
MeredithK
MeredithK
edited 06/07/24 in Formulas and Functions

Hello,

I am trying to write a formula that will blank out a cell if the value in that cell is equal to another.

Example: If "Open Invoice Amount" is equal to "Payment Rec'd", then blank out the cell under "Open Invoice Amount." If not, then subtract the open amount from the payment received and return that number.

Can anyone help?

Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/07/24 Answer ✓
    Options

    I rebuilt the formula in Smartsheet so now it should be copy and paste able.

    =IF(Helper@row = [Payment Rec'd]@row, "", Helper@row - [Payment Rec'd]@row)

    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.

Answers

  • Denden8706
    Options

    IN THE "Open Invoice Amount." CELL.

    =IF( [Open Invoice Amount]@ROW = [Payment Rec'd]@ROW, "", [Open Invoice Amount]@ROW - [Payment Rec'd]@ROW)

  • MeredithK
    Options

    I got the #UNPARSEABLE error message when I tried that formula.

  • Anjanesh Vaidya
    Anjanesh Vaidya ✭✭✭✭✭
    edited 06/07/24
    Options

    Hi MeredithK,

    You can try the following formula-
    =IF([Open Invoice Amount]@row = [Payment Rec'd]@row, " ", [Open Invoice Amount]@row - [Payment Rec'd]@row)

    I hope it will work.

    Thanks,

    Anjanesh Vaidya

    Smartsheet Development, Ignatiuz Software

    Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    How are you currently populating the [Open Invoice Amount] column? The formulas above will create a circular reference error if you drop them into the [Open Invoice Amount] column.

  • MeredithK
    Options

    I had a formula in the Open Invoice Amount column, but I got rid of that and just kept the numerical values. So, it's currently populated by number entry.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options
  • MeredithK
    Options

    Here's a screenshot of what I have setup so far:

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/07/24
    Options

    @MeredithK

    Reading what is it Your wanting to accomplish. You can not put everything in the same column. Whether it be [Open Invoice Amount] or [Payment Rec'd]. You would first need a hidden column. We can call this Helper. Have it be the location you put the amounts that you have in open [Open Invoice Amount]. And Hide it. THEN you can reference that Column ill call it helper.

    In the [Open Invoice Amount] column put this formula.

    IF(Helper@Row=[Payment Rec'd]@row,"",Helper@row-[Payment Rec'd]@row)

    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.

  • MeredithK
    Options

    I tried this in the Open Invoice Amount column:

    =IF([Helper]@Row=[Payment Rec'd]@row,"",[Helper]@row-[Payment Rec'd]@row)

    and I'm getting the #UNPARSEABLE error message.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/07/24
    Options

    @MeredithK

    Did you create a helper column?

    If you have teams I am more then happy to help you out with it.

    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 ✭✭✭✭✭✭
    edited 06/07/24 Answer ✓
    Options

    I rebuilt the formula in Smartsheet so now it should be copy and paste able.

    =IF(Helper@row = [Payment Rec'd]@row, "", Helper@row - [Payment Rec'd]@row)

    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.

  • MeredithK
    Options

    It worked - thanks so much!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!