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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
Geneview - Awesome approach!! Let me try this.
-
This worked perfectly!!
-
Wonderful! I'm so glad to hear that 🙂
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 207 Use Cases
- 517 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 83 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!