insert revenue or expense based upon date, amount and period

[shesez]

Team,

I need to have excel insert values in monthly sheet based upon start date month, period (say months) and monthly amount. - the values and periods will vary so I cannot just have fixed formula calculation

on one row lets say expected revenue amount of $1200 over the 18 months(note the amount and the period could vary based on calculations for each row of data) starting Jan 2012

I would like excel 2010 to make the calulation of $1200/ 12 and then place $100 in a cell under each month Jan to Dec (months will be shown above) until the period is reached.



Thanks

Shesez

[shaukat]

Hello,
it is not clear Please give an example workbook.

[shesez]

Hi,

the first post had an error with the period stated as 18 months and the formula showing divide by 12 instead of 18.

please see attached example

[rylo]

Hi

Try this

Please Login or Register  to view this content.

Or with formulas:
D3: =IF(AND(COLUMN()>=MATCH($B3,$2:$2),COLUMN()<=MATCH($B3,$2:$2)+$C3-1),$A3/$C3,"")
Copy down / across as required.
rylo

[shesez]

Guru,

Thanks! the formula version worked well.

However I would like to understand how the VBA code version works
I would appreciate any additional comments so I can understand how the code is working


Thankyou for your expert assistance!


Shesez

[rylo]

Hi

Inserted some commentry above each row of code

Please Login or Register  to view this content.

rylo