How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask John D Your Own Question
John D
John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9666
Experience:  Bachelor of Science - Engineering Consultant
Type Your Microsoft Office Question Here...
John D is online now
A new question is answered every 9 seconds

I have a Excel 2010.I have a Sales Forecast Sheet where

This answer was rated:

I have a Excel 2010. I have a Sales Forecast Sheet where I'm wanting the sales team to enter a monthly amount. This amount needs to be repeated across several months as we bill monthly depending on the length of the contract. For Example: the monthly amount is £100 and they forecast it to land in January and to last for 4 months. From this what I want to achieve on a separate sheet is £100 to be epopulated in a row for Jan, Feb, Mar, Apr. Does that make sense?


I'm happy to send you the forecast spreadsheet as that might make more sense.

Please attach your file using the paperclip so I can have a look and let you know if I'd be able to help with it under this question
Customer: replied 4 years ago.

Hi, The best place to start is on the 'Pipeline' sheet columns H,I & J


Using the spreadsheet attached (Row 4) as the example I'm wanting excel to automatically calculate counting 4 months from March 13 and then placing the 'Weighted Forecast' figure (Column J) in this example £81 in to months March, April, May & June on the 'Forecast' sheet.


Hopefully that helps.

Attachment: 2013-01-08_212730_partner_forecast_wip.xlsx


I've attached the file but it seems to only want to except a image. Let me know if I can send it via email if you have not received it.


Thanks for your help.

You already have formulas in column J. Are they incorrect?

So you want to add in the forecast sheet the values starting with the month stated in cell H4 for the number of months stated in cell I4, correct?
Customer: replied 4 years ago.

Yes, the formula in J is correct.


I have attached a revised spreadsheet which shows what I'm trying to achieve automatically.


On the 'Pipeline' sheet I have 3 opportunities. One starts in March and runs for 4 months, the other in January for 12 months and the last in August for 2 months. The weighted forecast (column J) is what I want to automatically appear in the 'Forecast' sheet for those months.


I've added the values on the 'Forecast' sheet (in yellow) to show the result that I'm try to automatically achieve.


Thanks for your help.


Attachment: 2013-01-08_225721_partner_forecast_rev2_wip.xlsx

Ok, finished writing the formulas in all the table

Here is what the result look like for the values shown in the red frame


Let me know if that's ok
Customer: replied 4 years ago.

Hi John,


This is looking good the only thing that needs amending is it seems to be adding on an extra month in the forecast.


June 13 contract length 3 months should finish in August 13 (not September)


February 13 contract length 8 months should finish in September not October. etc


I presume if a contract starts in Dec and runs for 5 months it would show it in Dec, Jan, Feb etc of the following year?


Many thanks.

Yes can exclude the extra month, and yes it will show next year provided the that year has been set up on the forecast table. Will finalize and send it shortly, In the meantime don't forget to add bonus as per the price adjustment report

Customer: replied 4 years ago.

Hi John,


I'm happy to add the bonus but I can't see where I'm to do it. Any ideas?



Thanks. After rating the answer you will get the bonus button. Thanks

Uploading the file...
John D and 2 other Microsoft Office Specialists are ready to help you

Thanks so much :)


Here you go



Attachment: 2013-01-09_165655_2013-01-08_225721_partner_forecast_rev2_wip1.xlsx



Hope this is ok. Please feel free to let me know if you have any questions






Customer: replied 4 years ago.

Hi John,


It could be me but I seem to be having a problem.


With the examples that in the spreadsheet the you supplied if I increase the length of the contract months it works fine. However, if I change the month then it doesn't update the Forecast sheet.


Also, if I add a new entry in the pipeline it doesn't appear in the Forecast....any ideas?



Hi again

I am not sure where you're changing the month and what you are changing it to. Could you please upload the file that has the changed month and let me know which cell that change is in
Customer: replied 4 years ago.

Hi John,


The cells in yellow are what I've changed. As you will see the forecast sheet hasn't been updated to show these changes.




Attachment: 2013-01-09_175606_partner_forecast_rev3.xlsx

I see.

The problem is you're not typing the date as real date but as text which Excel cannot understand it as date. Just type the date in cell H7 as date, i.e. 1-Feb-2013 and everything should be ok

Hope this helps
Customer: replied 4 years ago.

Great. I'll have a good play around with it. Thanks once again!

You're welcome :) Glad to have been able to help