Conditional Formatting Chapter 2!

Hello All (and apologies for lenght)

I posted a query on here the other day and it was answered expertly almost straight away - these are the follow up pleas for help!

Original Thread

Hello All

I'm in a new job where Excel is definitely not by comfort zone. However I am now the resident techie and I spend all my spare time reading excel forums and blogs (30 new functions in 30 days - my new life).

Right - I have a problem I cannot work out.

I have a resourcing spreadsheet. 12 columns one for each month of the year. Resources (people) are allocated to projects in rows. One resource can be on multiple projects. The first 5 columns are project, name, grade, etc. Then the months of the year.

Ideally each person should be not have more than 22 man days allocated across all projects - as they then would be at over capacity.

Each person, for a project, how a number of days allocated against a given month. Eg xx project. fred smith, Jan 10 days. Feb 3 days. March 14 days etc. A project can have between 1 and 30 resources working on it.

What I would like to do is use conditional formatting to make the background go red where you enter the number of days planned where the total number of days for that resources exceeds 22. I can easily do formatting where the sum of days is greater than any given number, but I am struggling with linking this for each resource.

It isn't easy to explain so I have uploaded an example of what I am trying to achieve.

I would be REALLY grateful for any pointers. I have got to the point of going around in circles.

Thanks

I had a lovely answer (see attached spreadsheet).

My next bit it in two parts. I know need to make the 'red' conditional on counting in whether the project is Active 'A' and excludes data if the project is on hold 'OH' or Completed 'C'.

I can see it might mean chaning the formula in the condidtional formatting to a sumifs. (I think)

However, (and this is part 2). If anyone could enlighten me as to why the formula appears to be the same in all the cells covered by the condidtional formatting - but it clearly works across different months and different resources. Why oh why? Is there some weird rules about conditional formatting which makes it relative somehow but this isn't reflected in the formula field?

If I could understand it - I can work out how to the multiple conditions I think. But in the mean time if anyone knows how to add the extra condition in that Column A needs to be an 'A' I would be very grateful. An explation would be an added bonus! I am not even sure if this is moon on a stick territory.

Thanks in advance.

Hi
Why are you using Excel when there are many many Project Management Applications around that do this for you and is far easier than using what is effectively a Financial Application not a Resource Management Application.
Tony

Hi we probably will go for an application (any recommended). But the procurement process is long and drawn out.

At the moment only 10 projects so excel it is.

Hi all, after much googling, I have found a few posts that would suggest this behaviours is a bug in excel 2007.

The formula does update to the correct relative value (as evidenced by the correct formatting being applied) but the formula itself does not update

How strange.

If anyone could help with other part of my query, ie how to include the extra condition of column a needing to be 'a', I would be very grateful.