Counting number of occurences from a drop-down list in Excel 2007 2

[Llwynog]

thread68-1668743
We are trying to make allocating tasks to employees fairer so that no-one is given more onerous tasks than anyone else.
I've got a workbook with 8 worksheets - 7 for days of the week - which the supervisors enter names against specific daily tasks,
the other has data linked from worksheets 1 to 7, to summarise, by day, who has been assigned to each of the tasks.
What I'd like to do is to be able to let the data entry person know when an employee has been allocated the same task for more than 4 times in any week (i.e. on the data entry sheet if an employee has been assigned a job more than 4 times then then the name would,say, appear in red font (or something similar), but I'm lost as to how to accomplish this.
Any help would be gratefully received.
Thank you

 

[SkipVought]

Hi,

If all your data were in one table on one sheet, it might be a simple COUNTIFS(), assuming your version is 2007+, or SUMPRODUCT() formula. Your workbook design makes this kind if simple analysis much MUCH more difficult!

So a solution might be
[tt]
=COUNTIF([EmpNameRange],[EmpName])
[/tt]
Assuming that the range is for 1 week.

However if it were me, I'd have ALL the weeks since day 1, in this table and use COUNTIFS() to count the employee in the current week. But you could also get data from ANY prior week, assuming you had a column for a week DATE, yes an actual date.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[byundt]

Because you are using Excel 2003, you can't use COUNTIFS or direct references to other worksheets in Conditional Formatting formula criteria. But you can disguise those references to other worksheets using INDIRECT function.

I set up an eight worksheet workbook where the Summary worksheet brings back all the assignments from the Sunday through Saturday worksheets with an INDIRECT formula like:
=INDIRECT(B$1 & "!" & CELL("address",$B2)) & ""
The concatenation of an empty string is to avoid bringing back a value of 0 for a blank cell on the supporting worksheet.

I then used the following Conditional Formatting "Formula is" criteria for column B on the supporting worksheets. It refers back to Summary for the count.
=AND(B2<>"",COUNTIF(INDIRECT("Summary!" & CELL("address",B2) & ":" & CELL("address",H2)),B2)>4)

Here is a sample workbook showing the above formulas in action

Brad