asked on In Excel I have data on leases payment amounts and payments dates. I need to show the amount of a payment in the correct row of payment date and in the column of the appropriate lease.
I have an Excel sheet showing lease details for 20 leases.
The main details are the payment amount each quarter and the dates of those payments.
Below each lease I have rows for their payment date.
I want the amount of each lease's quarterly amount (in row 6) to be shown in the relevant row (from 1/1/2017 below row 32) and in the column of the respective lease.
Excel-Showing-the-cost-alongside-th.xlsx
The file is attached.
How do I achieve this?
Thanks
Michael
The main details are the payment amount each quarter and the dates of those payments.
Below each lease I have rows for their payment date.
I want the amount of each lease's quarterly amount (in row 6) to be shown in the relevant row (from 1/1/2017 below row 32) and in the column of the respective lease.
Excel-Showing-the-cost-alongside-th.xlsx
The file is attached.
How do I achieve this?
Thanks
Michael
SpreadsheetsMicrosoft ExcelMicrosoft Office
Last Comment
Michael O'Flaherty
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you Saqib Husain, Syed
Your solution was spot on.
Rob,
your idea would be good for ongoing dates because it populates down as far as the columns go, but as the leases have finish dates with no payments after the last date the populated there needs to be no amounts shown for each lease after the term is over.
Thanks you both for your help.
I'm grateful.
Michael
Your solution was spot on.
Rob,
your idea would be good for ongoing dates because it populates down as far as the columns go, but as the leases have finish dates with no payments after the last date the populated there needs to be no amounts shown for each lease after the term is over.
Thanks you both for your help.
I'm grateful.
Michael
ASKER
Thanks
=IF(IFERROR(IF(MONTH($A33)
@Syed, I had the same idea to start with but then realised that will only match the first couple of months that are already populated in the table. I think the OP wants to see the payments against future dates as per the list starting in row 32.