Tracking remaining balance using data off two different worksheets

[tisoytigre]

Hi all! I'm new to learning excel, and I am in charge of my office's grant management, including tracking our invoices and remaining balances. I've created an excel workbook to log all the invoices we process, which I've attached here for you to view the format.

There's a tab called "Grantee In-House Tracking" where I track the invoices for our grants. This is for other people in the office to view to look at individual invoices if they have questions, so I need to keep this tab as is. I am now trying to create a tab called "Remaining balance" that sums up the balance remaining for each grant we have based on the invoices generated in "Grantee In-House Tracking."

I've tried to use SUMPRODUCT, SUMIF, and combinations of INDEX/MATCH to get the data on "Grantee In-House Tracking" tab to be summed up in the tab "Remaining balance." To no avail.

Can someone look over my sheet and tell me how to better write a formula to capture this information?

Here's a key to the different columns and what they signify:
Tracking ID: on "Grantee In-House Tracking," this is the unique ID for a specific invoice; it's just for tracking invoices systematically.
Grant No.: this number corresponds to a specific unique grant using our internal numbering system

I want basically for all the grants I have listed in the "Remaining balance tab" to show the following values:
1. The total amount that has been invoiced from the "Grantee In-House Tracking" tab
2. The resulting remaining balance

Thanks for helping me figure out why this data isn't being picked up between these two worksheets in my excel book!

Much appreciation,
-tt

[dosydos]

As there are multiple llines for some of these control nos. I made the assumption that the remaining balance will be based on the line with the most recent date paid in column Q.

This is an array formula so you need to use CTRL+SHIFT+ENTER when inputting the formula. If done so correctly, { } will be added around your formula.

In cell E3 of your remaining balance tab enter this formula with the above method and then drag down:

Please Login or Register  to view this content.

[tisoytigre]

Hey dosydos!

That didn't do anything---all that I got was a "-" to indicate a zero balance, which I know isn't true. Are you sure you wanted it to go to Column Q in the formula above?

I did make sure I used CTRL+SHIFT+ENTER, so not sure what else is causing the formula not to work.

-E

[dosydos]

seems to work for me with the example you provided. I have attached your worksheet with the formula put into the Remaining balance tab.

[FDibbins]

It could be that maxifS is not available in the OP's excel version. I have 2016 desktop and dont have that

[bebo021999]

It seems last occurence of each Control No's Remaning balance?
Try:

Please Login or Register  to view this content.

Drag down.

[tisoytigre]

Hi all!

Thanks for your collective help. It does seem that maxIFS doesn't work with my version of excel, but luckily bebo021999's solution does solve the issue!

Thanks again!

-E