Hello group, this is my first post so please consider that I am no expert (wouldn't be here otherwise).
I hope I'm posting in the right group/forum/
I am the Treasurer of a small club and I have created a spreadsheet to record payments of Dues and Arrears of members.
I have 5 columns allowing date and payment for each member to make a maximum of 5 payments throughout the year.
There is a column called Arrears to record the amount owing from previous years.
The Dues are £60 this year but may change over the years so I have put that single figure in its own cell outside the table which can be referenced in a calculation.
My problem in is calculating the amount paid in, easily done by simply adding the amounts, but the arrears must be cleared first before any money comes off the dues amount.
I also have a column called Remaining which records the total amount outstanding (of both Arrears and Dues)
The difficulty I have is 'overflowing' the amount paid once the Arrears have been paid, into the Dues column.
Also, needless to say, no amount can be a negative number.
I have tried simple addition and subtraction but as I said I'm no expert and while I think this may require a macro to solve (and I wouldn't know where to start on that), there may be other mathematical functions that I am not familiar with or have even heard of.
I have attached an example table in book1.xlsx (not actual names of members).
Bookmarks