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

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob Henson

Try this in B33 and copied down and across.

=IF(IFERROR(IF(MONTH($A33)=MONTH($A32),"",MOD(DATEDIF(B$2,$A33,"m"),3)),"")=0,B$6,0)

@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.

Michael O'Flaherty

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

Michael O'Flaherty

ASKER

Thanks