Forum Discussion
Change Color
I see the following patterns:
- colors are applied to cells with numeric values only, cells with "v" don't have special colors.
- each row has its own unique highlight color
- you already have two conditional formatting rules to color cells based on their value "v" (light amber) or "ab" (pink)
You say that the colors relate to projects and that projects could change on a weekly basis.
You could insert a column that specifies which project currently applies to the person. Then you can use conditional formatting and set up rules to format each cell with the project color if it has a number. Now you don't have to manually set the project colors and can apply the same conditional formatting
logic to the other sheets.
For example, insert a new column B after column A for the projects and enter the project names, in my example they are Project RED, Project AMBER and Project GREEN. Now create three additional formatting rules with formulas. The three formulas are (Starting with cell C4 and applied through to X6:
=AND($B4="Project RED",ISNUMBER(C4))
=AND($B4="Project AMBER",ISNUMBER(C4))
=AND($B4="Project GREEN",ISNUMBER(C4))
Insert the same column and conditional formats in all your sheets and get the project value from column B with a formula, like you do with the other values.
Now you can change the project values in column B and the colors will change accordingly.
I should have been more specific, I need to know the hours per project, per day. That way, I can monitor my progress and shift people to other project if needed.
Can you explain how that fits in the context of the Basic and weekly sheets? Where to you want to tally up the numbers?
I get nervous whenever I see a solution with "one sheet per (whatever unit)", because that is a nightmare for using formulas across the units.
That works great, thank you!
Nope, you did it right. The formula was set up for counting, and I made it more complicated than it needs to be.
To sum, change it to
=SUMPRODUCT(($C$2:$X$2=AB$4)*($B$5:$B$100=$AA5),$C$5:$X$100)
I've looked into your attached file, but I noticed this formula counts the cells, not the value inside the cells?
It could be I'm not using it right, if so, could you tell me what I did wrong?
You can do this with a Sumproduct() function. You need to insert a row that has the day name for each column (merged cells will not work). Let's assume this is row 2 (and you can hide that row).
Next you need to set up a grid with project names down the rows and weekdays across the columns. Inside this grid you can use a formula. My grid has project names in cells AA5 to AA7 and weekday names in cells AB4 to AG4. The formula starts in AB5 and assumes the schedule data goes down to row 100
=SUMPRODUCT(ISNUMBER($C$5:$X$100)*(($C$2:$X$2=AB$4)*($B$5:$B$100=$AA5)))
Copy across to Saturday and down to the last project name. See attached.
Tip: Avoid the use of merged cells. Use the formatting option "Center across selection" instead.
Haha, I can imagine it would be a nightmare if I needed to know all the hours per project, per day off all the sheets, but it's a little bit less complicated (I think).
I just need to know how many hours there are per day per shift, per week,
So, for example:
Let's say there are 3 projects and each project has to have a certain amount of "gross" hours for week 1:
- project A (500)
- project B (300)
- porject C (200)
Now, let's say I've got a total of 1300 hours scheduled in week 1, devided over the 3 projects:
- project A (600)
- project B (400)
- project C (300)
But, not all scheduled hours result in exual working hours (absense, sickness etc.), so after day 3 the "net" hours are:
- project A (200)
- project B (150)
- project C (175)
If I could see the hours per day per project, I could see how many hours there are scheduled for the remaining 2 days, and decide from which project I can extract people (in this example it would be project C, because I only need 25 more hours on that one)