Pulling Data from a work Rota

[Tyroneb90]

Hi All,

I'm hoping someone can help with an issue.

So I have a work rota - Employees names in collumn A, Then Collumn B - AQ is the days of the week / Date (01/08, 02/08, 03/08 etc..).

Each Cell in the row will either be Blank (White) to indicate the staff member is in or Black to indicate its their day off.

My request is can anyone think of or knows a formula that will let me calculate exactly who is in for a certain date?

On the back of that I would use that data and pull it through to another sheet which would show exactly who is in for that day.

(I've attached an image for reference)

Thanks in advance

 

[severynm]

Hi,

The first thing you'll need to change is relying on cell styles (font color, fill color, etc.) to store a value. This is a bad idea because it makes it so much more difficult to get the value of the cell and do any kind of calculations based on it. Define a value for each possible state: blank = in, O = off, H = ..., etc. Use letters, numbers, words, or whatever makes sense to you. Then once each cell has a value, you can use a formula like =COUNTIF(C4:C13,"") or =COUNTIF(C4:C13,"H") to get the number of people in a category for a given day.

If you still want each cell to visually appear as if there is no text as in your screenshot, then you could add a conditional formatting rule to change the cell fill and the font to the same color.

 

[Tyroneb90]

Thank you so much for this. I will give it a try!