Formulas and Conditional Formatting. Is there a better way to do this?

[mcm2022]

Hi,

I have an Office 365 sheet which shows a rota where a number of tasks can be allocated to staff members. The tasks are split into halves so they be carried out in the AM or PM of each day, either by a single member of staff or multiple members of staff. In Column T you can specify the number of staff required to carry out each task. Once that allocated number has been reached, the task should 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 for Task D even though it's 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 better way to highlight those tasks so that they calculate correctly?

Any help would be very-much appreciated.

Thanks,
Mark.

rota.xlsx
	A	B	C	D	E	F	G	H	I	J								R	S	T	U
1	BASE 1	Monday AM		Monday PM		BASE 2	Monday AM		Monday PM									Daily Tasks		Count
2	Person A	Task D		Task D		Person 1	Task 3		Task 3									Task 1	2	2	0.5
3	Office					Office												Task 2	#####	1	#####
4																		Task 3	0	2	2
5	Person B	Task D		Task B		Person 2	Task 3		Task 1									Task 4	1	1	0.5
6	Office					Office												Task A	#####	2	#####
7																		Task B	2	2	0.5
8	Person C	Task D		Task C		Person 3	Task 4		Task 3									Task C	2	2	0.5
9	Office					Office												Task D	0	2	2
10
Monday

Cell Formulas
Range	Formula
S2:S9	S2	=T2-U2
U2	U2	=IF(SUMPRODUCT((G:G="Task 1")+(I:I="Task 1")/2)=0,"",SUMPRODUCT((G:G="Task 1")+(I:I="Task 1")/1))/2
U3	U3	=IF(SUMPRODUCT((G:G="Task 2")+(I:I="Task 2")/2)=0,"",SUMPRODUCT((G:G="Task 2")+(I:I="Task 2")/1))/2
U4	U4	=IF(SUMPRODUCT((G:G="Task 3")+(I:I="Task 3")/2)=0,"",SUMPRODUCT((G:G="Task 3")+(I:I="Task 3")/1))/2
U5	U5	=IF(SUMPRODUCT((G:G="Task 4")+(I:I="Task 4")/2)=0,"",SUMPRODUCT((G:G="Task 4")+(I:I="Task 4")/1))/2
U6	U6	=IF(SUMPRODUCT((B:B="Task A")+(D:D="Task A")/2)=0,"",SUMPRODUCT((B:B="Task A")+(D:D="Task A")/1))/2
U7	U7	=IF(SUMPRODUCT((B:B="Task B")+(D:D="Task B")/2)=0,"",SUMPRODUCT((B:B="Task B")+(D:D="Task B")/1))/2
U8	U8	=IF(SUMPRODUCT((B:B="Task C")+(D:D="Task C")/2)=0,"",SUMPRODUCT((B:B="Task C")+(D:D="Task C")/1))/2
U9	U9	=IF(SUMPRODUCT((B:B="Task D")+(D:D="Task D")/2)=0,"",SUMPRODUCT((B:B="Task D")+(D:D="Task D")/1))/2
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A9	List	=Status
A3	List	=Status
A6	List	=Status
F3	List	=Status
F6	List	=Status
F9	List	=Status

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Conditional formatting/formula query
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[mcm2022]

Apologies. Thank you for adding the link. I'd be grateful for any help.

 

[6StringJazzer]

Note: this has been resolved at the cross-posted site.