Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tough formula to cause auto-updates in multiple cells
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tough formula to cause auto-updates in multiple cells
Hello Joe, I suggest you make the months actual dates, so E1 would be 1st Jan 2009, F1 1st Feb 2009 etc (all 1st of the month) - you can format as mmm-yy to show just month and year. Same applies to column D, make D2 1st Mar-09 and similar for D3, D4 etc and again format as mmm-yy Now you can use this formula in E2 copied across and down =IF(OR(E$1<$D2,E$1EDATE($D2,$C2-1)),"",$B2/$C2) regards, barry -- barry houdini ------------------------------------------------------------------------ barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=135060 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tough formula to cause auto-updates in multiple cells
I am absolutely floored Barry. BRAVO man - BRAVO!
THANK YOU! "barry houdini" wrote: Hello Joe, I suggest you make the months actual dates, so E1 would be 1st Jan 2009, F1 1st Feb 2009 etc (all 1st of the month) - you can format as mmm-yy to show just month and year. Same applies to column D, make D2 1st Mar-09 and similar for D3, D4 etc and again format as mmm-yy Now you can use this formula in E2 copied across and down =IF(OR(E$1<$D2,E$1EDATE($D2,$C2-1)),"",$B2/$C2) regards, barry -- barry houdini ------------------------------------------------------------------------ barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=135060 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
My Excel Chart No Longer Auto Updates | Excel Discussion (Misc queries) | |||
Tough one - Sum and IF with multiple referencing (array) | Excel Worksheet Functions | |||
Auto Updates by moving columns | Excel Discussion (Misc queries) | |||
Multiple Variables in a Commission Structure...a tough one!!! | Excel Worksheet Functions | |||
Auto updates - Clock | Excel Discussion (Misc queries) |