A | B | C | D | E | AA | AB | AC | |
1 | Invoices | |||||||
2 | Year | Remaining | Task | Funding | Invoice | Task | Fund | Amount |
3 | 2017 | 3014.03 | 97 | M | 0 | |||
5 | 2015 | 6460.71 | 98 | L6 | 60.71 | 98 | L6 | 60.71 |
7 | 2018 | 580.28 | 99 | F | 0 | 100 | A | 5600.74 |
10 | 2019 | 6956.39 | 99 | F | 0 | |||
11 | 2017 | 5510.74 | 100 | A | 5510.74 | |||
13 | 2018 | 8737.52 | 100 | A | 90 | |||
14 | 2019 | 5000.00 | 100 | A | 0 | |||
15 |
<tbody>
</tbody>
From visible rows, I need to subtract the Invoice amount $5600.74 from the remaining funds for Task 100 Fund A until invoice is zero - recording the amount of the invoice used under the invoice column. Notice some rows are hidden as this is a filtered table. Having a hard time writing a formula for cells in column E to handle subtracting the invoice amount until it has all been applied to the remaining funds (oldest year first). Perhaps a VBA function could handle this? Any suggestions would be greatly appreciated. |
<tbody>
</tbody>