Hi,
You moved the goal posts!
Now you split the year into 5 and 7 months instead of 2 x 6.
This formula will deal with any number of months:
- Code: Select all
=INDIRECT("$D" & 2 + MATCH($A10, $C$2:$C$6)-IF($A10=INDIRECT("$C" & 1 + MATCH($A10, $C$2:$C$6)),1,0))
But it will need more infrastructure.
I have based this formula on your column of Last Month payments to which I have added a row at the top that holds zero. This is needed by the MATCH function. MATCH compares the payment number in column A with the range of Last Months including the added zero at the top. It follows that you must have sequential numbers, starting with 1, in the rows where you list the payments.
The two INDIRECT functions require an Offset which is located relative to the Last Month list. I have adjusted the formula to look at the first row of the reference range (Last Months column). So, the INDIRECT in column D has the offset in the first row of the Last Months list, now starting with 0. The INDIRECT in column C counts the rows above the list. Therefore it is always one less than the offset in column D. The above formula has the first row of the Last Months list in row 2. Therefore the number of rows above it is 1.
It follows that you can't have your Last Months list start in row 1 without tweaking the formula.
All of this may be clearer when you look at the attached sheet where the formula has been put to use on the trial data you have provided.
I hope this will, finally, pass muster.
Have a great day!
You do not have the required permissions to view the files attached to this post.
Have a great day!
Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)