Forum Discussion
Conditional formatting : Reference a list of names to change cell color.
Hi Kyriakos,
Better to have the sample, but in general the rule could be
=COUNTIF(<business list with employees>,<employee>)>0
one rule for each color (other words for each business)
Hi Sergei. Attached is the Test roster file.
I tried your rule i think how it was meant to be used, but did not work.
Thanks for your assistance
Thank you. I'll play a bit with the file and answer
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