Forum Discussion
Conditional format Header and First column match
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
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
- Lorenzo
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?(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!
