Index/Collect Formula to create a Cashflow by Month...

Gary Thornton

I'm trying to forecast a cashflow model internally, and I'm having a bit of a brain blank on this currently! The first sheet below details the Project, Total Invoice Amount and four separate payments due with the corresponding months and years for each payment.

The second sheet details all four payments by the month/year on a single row. What I'm looking for is a formula to populate the relevant amount for each of the 4 separate payments from sheet 1 into the relevant month/year in sheet 2 (note I have manually updated the details for the first two projects in Sheet 2).

I'm pretty proficient on index/collect formulas, but adding dates into equation is causing me issues. I'm hoping that @Paul Newcome will be able to share some of his formula wisdom on this.

Paul Newcome

With your current structure, you are going to run into limitations a performance issues pretty quickly. My suggestion would be to pull the dates over from sheet 1 to sheet 2 for each payment and then reference those to populate the appropriate month columns on sheet 2.

Lets get those dates over first. We want to limit how many cross sheet references we are using and how many cells we are referencing. To do this I suggest 4 date type helper columns on sheet 1.

=DATE([1st Payment Year]@row, [1st Payment Month]@row, 1)

This should populate dates that are the first of each payment's corresponding month. From there we can use a standard INDEX/MATCH to pull those dates over into sheet 2.

=INDEX({Sheet 1 First Date Helper Column}, MATCH([Project Name]@row, {Sheet 1 Project Name Column}, 0))

I would also suggest using an additional 4 helper columns on sheet 2 to pull over each of the 4 payment amounts. Lets make sure we can get this working for all 4 payments on each row first.

Paul Newcome

I am always happy to help. I usually prefer single formula solutions as well, but sometimes efficiency requires help. Of course helper columns can always be hidden after setting everything up to kelp keep the sheet(s) looking clean.

Did you want to continue down this path even though it requires the extra formulas and helper columns?

Paul Newcome

With your current structure, you are going to run into limitations a performance issues pretty quickly. My suggestion would be to pull the dates over from sheet 1 to sheet 2 for each payment and then reference those to populate the appropriate month columns on sheet 2.

Lets get those dates over first. We want to limit how many cross sheet references we are using and how many cells we are referencing. To do this I suggest 4 date type helper columns on sheet 1.

=DATE([1st Payment Year]@row, [1st Payment Month]@row, 1)

This should populate dates that are the first of each payment's corresponding month. From there we can use a standard INDEX/MATCH to pull those dates over into sheet 2.

=INDEX({Sheet 1 First Date Helper Column}, MATCH([Project Name]@row, {Sheet 1 Project Name Column}, 0))

I would also suggest using an additional 4 helper columns on sheet 2 to pull over each of the 4 payment amounts. Lets make sure we can get this working for all 4 payments on each row first.

Gary Thornton

Thanks @Paul Newcome . I'll be honest, I was hoping that there would be a single formula to bring the results back into sheet 2, but I can understand your approach and noted on the potential performance issues with the cross sheet references.

Thanks again for your help.

Paul Newcome

I am always happy to help. I usually prefer single formula solutions as well, but sometimes efficiency requires help. Of course helper columns can always be hidden after setting everything up to kelp keep the sheet(s) looking clean.

Did you want to continue down this path even though it requires the extra formulas and helper columns?

Gary Thornton

I've already implemented your proposal, and it works as required. Thanks again for your help on this...

Paul Newcome

Ah. Ok. I hadn't yet thrown in the bit about spreading it out across the Jan/Feb/Mar columns. Just wanted to make sure you were squared away with your full ask.