[Daniel Miz]

Hi all,

Ok ahead of me is very complex scenario that I've been trying to solve for days now. Hopefully someone would be able to help me.


So, this is the scenario plain and simple;

I have a certain amount of time in months inserted in Sheet 1 as well as the cost for that specific duration. Now the tricky bit is putting together a 24 month timeline.


Total no. of months go into cell Q9

Total Costs go into cell O3


Now I know that whatever the inserted time in months may be:

A. 40% (allocated in cell P6 in %) of the total duration is allocated to Stage A (allocated to Q6 in months)

B. 35% (allocated in cell P7 in %) of the total duration is allocated to Stage B (allocated to Q7 in months)

C. 25% (allocated in cell P8 in %) of the total duration is allocated to Stage C (allocated to Q8 in months)


Now each stage represents a different percentage in cost as well:

A. Stage A represents 45% (shown in cell R6 in %) of the cost

B. Stage B represents 45% (shown in cell R7 in %) of the cost

C. Stage A represents 10% (shown in cell R8 in %) of the cost


I need to create a sort of a timeline running in months that according to the duration of the project, will automatically feed the data in so if it's not stage A, then it's B, and if it's not B, then it is C.

Now accurdingly, stage A has to occur at least once, so I did the 1st cell of the timeline easy.

The 2nd cell was also quite easy as it's got only the selection option of being either stage A or stage B.

cell 3 onwards is the problem.


If anyone can help and throw suggestions it would be much appreciated.