Hey this probly may be easy for guys who know formulas and functions but I'm out of depth.
I work for a not-for-profit supported accommodation programme. I am constructing an excel spreadsheet to show balance either owing or in advance.
Payments may be received from different sources and not necessarily at predictable date intervals.
The amount owing consists of 3 components: Rent and Lodgings costs both must accrue weekly in separate columns and there may be occasional One-off costs to be added in from a third column. These three are totalled in a separate column.
I have columns showing commencement date, weekly rent rate, weekly lodgings rate. I have other columns to record amounts paid and the date paid.
I need the spreadsheet to show
Balance (at today's date), and
Date paid up to
but the row of data should remain blank until a payment amount is recorded in that row.
So somehow I need Payments Received processed to show what date the client is paid up to;
then I need the costs accrued as of Today to be adjusted by Payments received to show current balance.
Any ideas how I can construct formulae/function to achieve these?
I work for a not-for-profit supported accommodation programme. I am constructing an excel spreadsheet to show balance either owing or in advance.
Payments may be received from different sources and not necessarily at predictable date intervals.
The amount owing consists of 3 components: Rent and Lodgings costs both must accrue weekly in separate columns and there may be occasional One-off costs to be added in from a third column. These three are totalled in a separate column.
I have columns showing commencement date, weekly rent rate, weekly lodgings rate. I have other columns to record amounts paid and the date paid.
I need the spreadsheet to show
Balance (at today's date), and
Date paid up to
but the row of data should remain blank until a payment amount is recorded in that row.
So somehow I need Payments Received processed to show what date the client is paid up to;
then I need the costs accrued as of Today to be adjusted by Payments received to show current balance.
Any ideas how I can construct formulae/function to achieve these?