Automating a sales forecast based on a start and end date for revenue

[adam_d_john]

Hello,

I have a table (see attached) that has the product name; revenue per month; start date when revenue begins and end date for when revenue stops. I would like to automate my sales table (providing monthly sales) so that the revenue starts on the date the table indicates and ends on the date the table indicates -- for each product.

Does anyone know how to do this? I attach a sample spreadsheet for reference.

Any help would be much appreciated.

Many thanks!

[JohnTopley]

In G3

=IFERROR(INDEX($D$2:$D$5,MATCH(1,($A$2:$A$5=$F3)*(G$2>=$B$2:$B$5)*(G$2<=$C$2:$C$5),0)),"")

Enter with Ctrl+Shift+Enter

BUT in row 2

Set dates to Excel dates i.e. 01/01/2017, 01/02/2017 (dd/mm/yyyy) and then format as "mmm".

ALWAYS use proper Excel Dates NEVER TEXT!

[adam_d_john]

I have updated the spreadsheet with the proposed formulas (I added it to the original post), but it only provides data for product C. I re-attached the updated spreadsheet here.

I was trying to have the same monthly revenue figure applied to all the cells between the start date and the end date. For example: Product A would have $200 in all months between march and December 2017.

Is this something that is possible to do?

Many thanks for your assistance!

[JohnTopley]

That is what the file in #2 does. Did you actual look at it : changed dates in row 2 ????

plus your range is incorrect as you put the dates in rows 9 to 12!

[JohnTopley]

See the attached ..

[adam_d_john]

Hi John,

Apologies - It seems I messed up the rows.

Thank you for fixing and sending over. Much appreciated!

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[adam_d_john]

Done. Thanks again.