Formula to blank out a cell
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

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

IN THE "Open Invoice Amount." CELL.
=IF( [Open Invoice Amount]@ROW = [Payment Rec'd]@ROW, "", [Open Invoice Amount]@ROW  [Payment Rec'd]@ROW)

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

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⬆️

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.

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.

Are you tracking invoices paid anywhere?

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

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.

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.

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.

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.

It worked  thanks so much!!
Help Article Resources
Categories
Check out the Formula Handbook template!