RemainingBalance to show on Source Sheet

Tnorman

Hello All! What is the best way to maintain a remaining balance? In my scenario, I want to show the remainng balance after each new invoice record.

INVOICE SHEET

I tried the following in the Remaining Balance field:

=[PO# Award Amntt]@row - SUMIFS([Inv. Amnt]:[Inv. Amnt], Vendor:Vendor, =Vendor@row, [FYar]:[FY], =[FY]@row, [P.O. #]:[P.O. #], =[P.O. #]@row)

This is what I get:

Genevieve P.

Hi @Tnorman

Thank you for clarifying! In this case, we would need to change your ranges to only look from the top row of the sheet to the current row, instead of looking through the entire column.

=[PO# Award Amntt]@row - SUMIFS([Inv. Amnt]$1:[Inv. Amnt]@row, Vendor$1:Vendor@row, =Vendor@row, [FYar]$1:[FY]@row, =[FY]@row, [P.O. #]$1:[P.O. #]@row, =[P.O. #]@row)

Then you can drag-fill this down the column so it applies to all the cells. Note that you won't be able to make this a column formula due to locking the top bit of the formula to the first row with $1. However, formulas will still auto-fill even if they're not a column formula - see: Use or Override Automatic Formatting and Formula Autofill

Let me know if this is better!

Cheers,

Genevieve

Genevieve P.

Hi @Tnorman

This formula looks to be correct! It's subtracting the total Invoice Amount has has come in for that PO across multiple rows from the current "PO # Award Ammnt" cell in this current row.

However, do you mean that each new row has a new Award Amount that you need to SUM as well?

In that case, you'll need another SUMIFS at the beginning:

=SUMIFS([PO# Award Amntt]@row:[PO# Award Amntt]@row, Vendor:Vendor, =Vendor@row, [FYar]:[FY], =[FY]@row, [P.O. #]:[P.O. #], =[P.O. #]@row) - SUMIFS([Inv. Amnt]:[Inv. Amnt], Vendor:Vendor, =Vendor@row, [FYar]:[FY], =[FY]@row, [P.O. #]:[P.O. #], =[P.O. #]@row)

Let me know if this is what you were looking to do or not!

Cheers,

Genevieve

Tnorman

Hello Genevieve,

Thanks for responding, I was hoping to show the Remaining balance decrementing values over time. However once a row comes in all PREVIOUS rows matching the criteria gets updated to reflect the "CURRENT" Remaining Balance amount.

Genevieve P.

Hi @Tnorman

Thank you for clarifying! In this case, we would need to change your ranges to only look from the top row of the sheet to the current row, instead of looking through the entire column.

=[PO# Award Amntt]@row - SUMIFS([Inv. Amnt]$1:[Inv. Amnt]@row, Vendor$1:Vendor@row, =Vendor@row, [FYar]$1:[FY]@row, =[FY]@row, [P.O. #]$1:[P.O. #]@row, =[P.O. #]@row)

Then you can drag-fill this down the column so it applies to all the cells. Note that you won't be able to make this a column formula due to locking the top bit of the formula to the first row with $1. However, formulas will still auto-fill even if they're not a column formula - see: Use or Override Automatic Formatting and Formula Autofill

Let me know if this is better!

Cheers,

Genevieve

Tnorman

Geneview - Awesome approach!! Let me try this.

Tnorman

This worked perfectly!!

Genevieve P.

Wonderful! I'm so glad to hear that 🙂