Excel question on rolling months

[Hasit]

I have an Excel spreadsheet which calculates resource requirements for projects that my team runs. The resource requirements are based on pretty good estimates of productivity, so I am able to create the spreadsheet for 12 months relatively easily.

Heres the question:

I would like a rolling 3 months (for the current month plus 2 further months), where I get to see the detail for those months, PLUS have the ability to override the numbers, if I need to - WITHOUT having to go back to the original formulas that calculated the figures. I then want to add all the resource numbers by skill set (currently 6 skill sets), but add the overridden numbers if it exists.

Now, I can extract this data pretty easily from the already calculated numbers, but its automagically performing the rolling 3 months, PLUS the ability to override the numbers if necessary - thats causing the problem. The overriding can be done by entering data in an adjacent column. BUT, If then how would I perform additions if on mutiple columns whether there is data in the "override" cell or not.

One other point - no VBA, I want to be able to hand this over to my admin, who has some Excel skills, but knows no VBA.

Anyone any thoughts?

 

[Hasit]

Apologies, suddenly lost the ability to write coherent English. The last last of the last paragraph which reads:

"BUT, If then how would I perform additions if on mutiple columns whether there is data in the "override" cell or not.
" should read:

"BUT if thats the case, how would I perform additions on multiple columns by skill set, when there is data in the "override" cell/column."

 

[briand2]

I'd love to help, but I can't quite picture the spreadsheet layout. Perhaps you could post the column names (or the most relevant ones) and a couple of rows of data?

Brian

 

[Loomah]

I echo Brian's comments but another alternative is to email a copy of the book.
I generaly won't post my email address so if you would like someone to have a look you could post your own email and I'll contact you.

That said I can't envisage a totally VBA free file but the trick is to make it so your user doesn't notice it - kind of!

Happy Friday
;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[Hasit]

Sure. Heres what I have:

[tt]
| A | B | C | D | E | F | G |
-------------------------------------------
|Name | PM | Dev | Time| Jan | Feb | Mar |
-------------------------------------------
| XYZ | AB | CD | 6 | 0.08| 0.08| 0.08|
[/tt]

What I want to do is have a rolling 3 months which show (in this case) January thru March for project XYZ, by skill set (in this case Project Manager, Development).

What I want to be able to do it to override the 0.08 number if I believe that the PM (for example) is working harder than that. I then want to add up the total resource committment, and add the override number, but only if it exists. With 6 skill sets, the IF clause could be very complex! VLOOKUP is an option and I am looking at that right now.

My biggest problem is trying to get the rolling months to work automatically. So next month I want to see Feb thru April.

Does that make sense?

 

[Hasit]

Loomah/briand2 : email address hasitbakhda@yahoo.co.uk

 

[Wray69]

for the rolling months, in the first month do =TODAY() for the next two do the following =DATE(YEAR(A1), MONTH(A1)+1, DAY(A1)) and then custom format the cells for MMM

Hope this helps,

Regards,

Wray