Forum Discussion
Conditional formatting : Reference a list of names to change cell color.
Thank you :)
Im not sure if it will make a difference.. but the employee lists in reality are far larger and could have 30- 50 employees per contractor. Just incase there is a limit on some rules, somehow...
Hi Kyriakos,
If your actual data is structured as in sample the rule could be as
=COUNTIF('Employee list'!$E$1:$E$90,OFFSET($A$3,0,INT((COLUMN()-1)/3)*3+1-1))>0where we compare for every 3 columns the value which is in first cell of each of such 3 cells. Here are 3 identical rules for each color
which are applied to your entire range. If new columns appears you may expand the range or apply these rules from one column to another Format painter. The only point you use merged cells, with Format painter you have to apply rules separately for first row with merged cells and for other ones - that will generate a lot of additional rules.
In general better to avoid merged cells at all, the generate only issues. I removed merging for the first row. Instead you may select three cells with the value in first one and apply format (Ctrl+1) with Central Across Selection for the horizontal text alignment
Effect will be the same as for merging but avoid potential issues. At least Format painter will work correctly for entire column.
Please see attached
Hi Sergei
Thanks for your help
I do have some queries though, your rule worked pefectly, i have slightly adjusted how it shows as only where the name goes had to be colored.
However i have now run into issues of how to get the second set of Employee rows to change color. B3 Employee 2 can work on the same day as B1 Employee 1 so need to be able to show this.
Does anything need to be changed in the formula or is it an issue of Merged cells like you mentioned?
Kind regards,
See attached for exampleHi Kyriakos,
Does that mean you need two criteria - one is for which business the employee is belong to (lists in second sheet) and in the roster for which business this or that employee works at given day?
And what to color, first or second one?
I didn't find in your sample such situation, not clear for me what is required.
Sorry.
What i meant, is that employees names will be put in rows 3-5-7-9 (can be employees from different business's)
Your example coloured the entire column.
My current issue is that i made the rule work for just row 3, however when i copied it over to row 5 it would change color dependant on row 3.