# Solved Excel - formula to add column to range

February 26, 2017 at 21:31:34
Specs: Windows 64
 I have a forecast spreadsheet that currently needs manual input to add appropriate numbered monthly columns based on a project time frame. ie. 12 month project requires 12 monthly columns. Ideally what I would like is to have project start and finish date cells which are linked to a formula or macro which will spread the forecast across the correct number of monthly columns. Is this possible?

See More: Excel - formula to add column to range

#1
February 27, 2017 at 00:34:57
 If I am understanding this correctly, what you are looking for is to spread your forecase spend evening between your forecast dates? so if you forecasted £100 spend between periods 01/01/2017 and 01/05/2017 which is 5 monthsthen your £100 over 5 would give you a monthly spend of £20So this is pretty straight forward IF your are always going to spread using an EVEN curve, ie, your spread will always be the same for each period, rather than a front loaded or back loaded curve. Something like this will work, however please note this has been crudely put together as an example ONLY, there is much room for improvement, I just wanted to prove a concept. for this to work you need the following ``` A B C 1 Forecast Start Date End Date 2 £100.00 01/01/2017 01/05/2017 ```Then run the code and it will create the spread across D E F G H```Dim NoMonths As Long Sub SpreadForecaste() NoMonths = DateDiff("m", Range("B2"), Range("C2")) For i = 4 To (NoMonths * 2) Cells(2, i).Value = Range("A2") / (NoMonths + 1) Next i End Sub ```