Formula to blank out a cell

MeredithK

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?

Mark.poole

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)

Denden8706

IN THE "Open Invoice Amount." CELL.

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

MeredithK

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

Anjanesh Vaidya

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.

Paul Newcome

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

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

Are you tracking invoices paid anywhere?

MeredithK

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

Mark.poole

@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)

MeredithK

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

@MeredithK

Did you create a helper column?

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

Mark.poole

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)

MeredithK

It worked - thanks so much!!