So, I'm tracking payments of invoices. I want to use the RGY balls but only the Green and Red balls to be designated in my Paid in Full? column. I am using the Invoice Amount and Payment columns to set up my formula. So far, the first part of my formula works. Whenever the invoice amount equals the payment, the Paid in Full ball is green. Not sure how to continue the formula for the rest of the criteria. We want to also see a red ball if the payment amount is anything other than the invoice amount, "red" and for new entries, the paid in full? to show blank until a payment amount is entered. I hope this makes sense. Plus, I want to convert the column formula for the Paid in Full? column but it's not letting me. Any help with this is greatly appreciated. Below is my sheet and this is my formula that I think is not constructed correctly:

=IF([Invoice Amount]@row = Payment@row, "Green", "Red")

So again, the blank row needs to show blank until an entry is made, not green like seen below and again, how can I convert this formula for the column or is it not doable? This was a template (can't remember the name) that I found. I liked how it counted the number of invoices processed, plus the fact that the Account Balance shows $0 balance when the invoice is paid or the balance owed of open invoices to pay. At a glance we can see where we are. Also, when I said we wanted to see red...even though it's paid, the red prompts us to check the comments for the reason why we may have paid a different amount. Anyhow, I hope this makes sense and I look forward to your response.

Best Answers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!