Hi MRafik,
The problem was that your reference to the start date was relative.
Meaning that:
1 month + 31-1-2013 (B8) = 28-2-2013 (B9)
1 month + 28-2-2013 (B9) = 28-3-2013 (B10)
Making the reference absolute was the first step, but then we need something to add months.
Otherwise we would get:
1 month + 31-1-2013 (B8) = 28-2-2013 (B9)
1 month + 31-1-2013 (B8) = 28-3-2013 (B10)
[ CELL("row",B9)-8 ] is just used as a counter.
[ CELL("row",B9) ] will result 9 as is the row number of B9.
Deduct 8 from 9 = 1 or 1 month.
For the next row the result will be 10 as is the row number of B10.
Deduct 8 from 10 = 2 or 2 months.
and so on...
So for monthly the counter would need to add 1.
But for Quarterly the counter would need to add 3.
So think about how you can get 3, 6, 9 ...
Solution:
Use row of B1 = 1, times 3 = 3
Next cell would be B2 = 2, times 3 = 6
Thus part of formula would be:
IF($AA$2=4,EDATE(B$8,CELL("row",B1)*3)
Now that I think about it a second time this basically solves it all, right?
Monthly: IF($AA$2=3,EDATE(B$8,CELL("row",B1)
Quarterly: IF($AA$2=4,EDATE(B$8,CELL("row",B1)*3)
Half Yearly: IF($AA$2=6,EDATE(B$8,CELL("row",B1)*6)
Annually: IF($AA$2=7,EDATE(B$8,CELL("row",B1)*12)
Best regards,
Trowa