Tough formula to cause auto-updates in multiple cells

[Access Joe]

Hey everyone,

Excel 2007. This one is challenging - don't even know if it can be done.
My current layout:

A B C D E F G H
Project budget months start jan feb mar apr
a 50000 5 mar
b 20000 6 may
c 25000 4 sept
Taking "Project A", I would like the divide the given budget across five
months starting in March (so on that row for example, you would see "$10,000"
for each month from Mar through July). Same for Project B - it starts in May
and lasts 6 months. My budget is 20,000, meaning each month has a budget of
about $3300. Sounds easy - here's the catch.

I want to be able to change any one of the parameters for Columns B, C, or D
AND have the corresponding monthly breakdownds update automatically. For
example, if I changed the Start Month for Project A to January (instead of
March), I want those individual "$10,000" numbers to automatically move so
they begin in Jan and now end in May. Likewise, if I changed the length of
any project (i.e. make Project B "8" months instead of 6, I now want to see
$2500 for those eight months (May - Dec) instead of $3300 for the original
six.

Hope that makes sense. I don't have confidence this can be done, but I'm
hoping someone out there can help. THANK YOU!

Joe

 

[barry houdini]

Hello Joe,

I suggest you make the months actual dates, so E1 would be 1st Ja
2009, F1 1st Feb 2009 etc (all 1st of the month) - you can format a
mmm-yy to show just month and year.

Same applies to column D, make D2 1st Mar-09 and similar for D3, D4 et
and again format as mmm-yy

Now you can use this formula in E2 copied across and down

=IF(OR(E$1<$D2,E$1>EDATE($D2,$C2-1)),"",$B2/$C2)

regards, barr

 

[Access Joe]

I am absolutely floored Barry. BRAVO man - BRAVO!

THANK YOU!