Conditional formatting/formula query

Cross-posted at https://www.mrexcel.com/board/thread...-this.1215670/

Hi,

I have created a Rota sheet where a number of tasks can be allocated to staff members. Tasks are split in half so they can be carried out AM and/or PM, either by a single member of staff or multiple staff members.

You can specify the number of staff required to carry out the task. Once that allocated number has been reached, the task will highlight in green in the Daily Tasks list on the right of the sheet.

In the sheet, for Base 1, the required amount of staff for Task D is 2 (i.e. 2 staff for AM and 2 staff for PM). The Daily Total is highlighting green even though Task D is allocated to 3 staff members during AM. Base 2 shows this correctly, where Task 3 is allocated between 2 staff members and split evenly across AM and PM.

Is there a way to only highlight those tasks correctly? Any help would be gratefully appreciated.

Thanks in advance,
Mark.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

I have added the link to your post since you are a new member. Please review the rules.

Even though what you are trying to do sounds simple, I am having trouble mapping your description to what I see in your file.

What is a "Base"?

How do you know the required amount of staff for a Base/Task/AM-PM?

In the sheet, for Base 1, the required amount of staff for Task D is 2 (i.e. 2 staff for AM and 2 staff for PM).

I don't see how you can tell this from the file.

I think it would help if you gave more of a walkthrough about how your file works. Remember, you know all about it, but we've never seen it before and don't know anything about what you are doing except what you put in your post.

Your design is rather complicated for what you are doing and I suspect that is because you designed it for how it will look rather than the relationships among the data. This requires you to duplicate all your conditional formatting rules. For example, for a cell to be green you have separate rules for each cell in column R. I am not going to offer a redesign here but just try to offer a patch for the problem you have.

I have replaced the formulas in column U, and removed the redundant rules for green in column R with a single rule that works for every row. This also resolves the #VALUE errors you were getting.

Glad to help! You can rewrite the CF rules for black to one rule, following the same example as the one I did for green, if it's worth the bother for you.

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking addreputationiconsmall.jpg below their name.

Originally Posted by 6StringJazzer

Glad to help! You can rewrite the CF rules for black to one rule, following the same example as the one I did for green, if it's worth the bother for you.

Sorry, I should've mentioned that a black background denotes when a task has been over-allocated. For example, if Task 1 requires 3 staff but has been allocated to 4 members of staff, that's when it's highlighted. I've tried =$S2<0 but it doesn't work. What's the best Conditional Formatting rule for this?

Thanks in advance.

My solution counts the total number of shifts assigned and ensures that the minimum is met. It doesn't check for being over the minimum. The easiest way to do this is with a separate formula. (It might be possible to do all of this with a single formula but using two is easier to write and easier to understand.)

Column S is not required. I have updated the green rule to compare T and U. (If you wanted to, you could even eliminate the formula in column U and put the logic into the CF rule.)

I have added a formula in column V and modified a single black rule to check it.

Note that you can have a situation where one shift is overallocated and another is underallocated. In this particular file, that is Task D overallocated in AM. You get a black cell in this case. Once you resolve the overallocation, the cell will turn red to show you are underallocated for PM.

You're welcome!

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking addreputationiconsmall.jpg below their name.