Forum Discussion
Solved
Conditional format Header and First column match
Hi all,
I have a table with the name of employees in the header. Dates are in the first column of the table.
There could be multiple rows with the same date.
Also, I have a table with the names of the employees and the dates they are on holiday.
Is there a way to conditional format a cell in Table1 based on the info in Table 2?
I was thinking about adding a column with FindCellocation, and then make that the reference to the conditional format. But, not sure how to, and how to avoid the problem with multiple rows per date.
See attached file for more clearity.
Hi Celia9
(You don't use Tables but Ranges)
- Setup TableHolidays as shown in J2:K8
- Named range J3:J9 EmpNames
- Named range K3:K9 EmpHolidays
- Selected C2:H9 > Conditional Formatting > New Rule > Use a formula to determine...:=SUM(--ISNUMBER(MATCH(EmpNames,C$1,0) * MATCH(EmpHolidays,$A2,0)))Notes:
- Used a Table so new entries will be taken into account automatically (a Table auto-resize when you add a new entry in the 1st row following the bottom of the Table)
- Named the Table's ranges as we can't MATCH (or similar) in a Table when doing Cond. Formatting
4 Replies
Hi Celia9
(You don't use Tables but Ranges)
- Setup TableHolidays as shown in J2:K8
- Named range J3:J9 EmpNames
- Named range K3:K9 EmpHolidays
- Selected C2:H9 > Conditional Formatting > New Rule > Use a formula to determine...:=SUM(--ISNUMBER(MATCH(EmpNames,C$1,0) * MATCH(EmpHolidays,$A2,0)))Notes:
- Used a Table so new entries will be taken into account automatically (a Table auto-resize when you add a new entry in the 1st row following the bottom of the Table)
- Named the Table's ranges as we can't MATCH (or similar) in a Table when doing Cond. Formatting(Glad I could help & Thanks for providing feedback)
Do I need to change anything about the formula after I convert the ranges to tables? Or can I still use it like you explained?
Not 100% sure I understand but if you talk about converting the 2 Ranges you shared as Tables then the answer is No, expect if the Table with Datum, week, Employee1...Employee6 doesn't start in row #1
Any issue let me know + please share at least a picture showing where Table with Datum, week, Employee1...Employee6 sits on the sheet
- Oops I forgot to turn it into tables again after copy and paste from original file.
Thank you so much, I will transfer it to the original document! Hero!
