RemainingBalance to show on Source Sheet
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:
Best Answer
-
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
Answers
-
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
-
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.
-
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
-
Geneview - Awesome approach!! Let me try this.
-
This worked perfectly!!
-
Wonderful! I'm so glad to hear that 🙂
Help Article Resources
Categories
Check out the Formula Handbook template!