I have a roster that is used to collate data based on 2 criteria:
1. what role the person was in (using initials)
2. if that role was rostered on (R), on standby (SB) or part of an Incident (INC) by cell colour
For each role and person I have to sum the number of days, so the end data looks like this:
Days rostered on Days on Standby Days incident occured
MF 60 20 5
* All days use a colour code:
- Rostered has no color
- Standby is colored orange
- Incident is colored peach
* Criteria is - You are rostered on for all days, you can be on standby for any of those rostered days and an incident could occur any day you are rostered. So in the example above, MF was rostered for 60 days, 20 of those they were on standby and on 5 days an incident ocurred. 60 is the total days MF was in this project, not 60 + 20 + 5 = 85
In my working files I have a VBA code (by someone else) that picks up the color and counts the cells (unfortunately it wouldn't work in the sample sheet so I have inserted the text) but it does not allow me to then count what person was in the role on that day. I'd like to be able to do the calculation off the roster sheet if possible.
However, we have another sheet showing cost codes (sheet WBS) which shows what days each role was listed as. I could use that for the Standby and incident days but I still need toshow what person was on the roster for that role each day.
I was hoping that the formula ,=CountCellsByColour(roster!C4:NG4,<cell color>) could be matched with a =COUNTIF(Roster!$C$4:$NQ$4,Stats!$A3) formula to grab both.
Can someone assist in making this happen?
1. what role the person was in (using initials)
2. if that role was rostered on (R), on standby (SB) or part of an Incident (INC) by cell colour
For each role and person I have to sum the number of days, so the end data looks like this:
Days rostered on Days on Standby Days incident occured
MF 60 20 5
* All days use a colour code:
- Rostered has no color
- Standby is colored orange
- Incident is colored peach
* Criteria is - You are rostered on for all days, you can be on standby for any of those rostered days and an incident could occur any day you are rostered. So in the example above, MF was rostered for 60 days, 20 of those they were on standby and on 5 days an incident ocurred. 60 is the total days MF was in this project, not 60 + 20 + 5 = 85
In my working files I have a VBA code (by someone else) that picks up the color and counts the cells (unfortunately it wouldn't work in the sample sheet so I have inserted the text) but it does not allow me to then count what person was in the role on that day. I'd like to be able to do the calculation off the roster sheet if possible.
However, we have another sheet showing cost codes (sheet WBS) which shows what days each role was listed as. I could use that for the Standby and incident days but I still need toshow what person was on the roster for that role each day.
I was hoping that the formula ,=CountCellsByColour(roster!C4:NG4,<cell color>) could be matched with a =COUNTIF(Roster!$C$4:$NQ$4,Stats!$A3) formula to grab both.
Can someone assist in making this happen?