0

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.

https://docs.google.com/spreadsheets/d/1ASz_DAsUB-vInYRyDCdTTMEahx1lW34iYrH0y300vik/edit?usp=sharing

Not sure if I'm clear. Any help would be much appreciated.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.