Formula to blank out a cell

Options
edited 06/07/24

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?

• ✭✭✭✭✭✭
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.

• Options

IN THE "Open Invoice Amount." CELL.

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

• Options

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

• ✭✭✭✭✭
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

• ✭✭✭✭✭✭
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.

• 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.

• ✭✭✭✭✭✭
Options

Are you tracking invoices paid anywhere?

• Options

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

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

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.

• 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.

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

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.

• ✭✭✭✭✭✭
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.

• Options

It worked - thanks so much!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!