I'm facing a difficult challenge (at least for me) with Google Sheet. I receive invoices all year long and I make deposit all year long that should offset invoices. However deposits values are independent from invoices values. Therefore I have to allocate each deposit to last invoice still open, and quite often, a deposit has to be split between 2 invoices (one part to offset the remaining balance of an invoice, and the remaining balance of the deposit to the next open invoice.
For now, all this "allocation" of deposits has been done manually by the intern but it takes a lot of time and it is not very efficient, particularly when an invoice was forgotten in the past, is added to the table and all previous allocations have to be redone manually.
I have been trying with the query and filter functions however with unsatisfying results so far as the only option I found is to have a column with all invoices and then have the deposit references in raw. It only works invoices by invoices as I retrieve the deposits for each invoices; but I can't drag the formula to apply it to all invoices.
I would have preferred to have a first column with the invoice number and a second column with all allocated deposits.
Not sure if I'm clear. Any help would be much appreciated.