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 https://www.bing.com/ck/a?!&&p=f6aaf34c9625aa63JmltdHM9MTY1ODczNDE2MiZpZ3VpZD1lMzMwMDM1NS03ZGFjLTQwZjktOGNjMS05MzBmMmNkZmM1ZWYmaW5zaWQ9NTE3MQ&ptn=3&hsh=3&fclid=80776687-0beb-11ed-beda-d2daf567e3ca&u=a1aHR0cHM6Ly9zdXBwb3J0Lm1pY3Jvc29mdC5jb20vZW4tdXMvb2ZmaWNlL2Zvcm1hdC1hbi1leGNlbC10YWJsZS02Nzg5NjE5Zi1jODg5LTQ5NWMtOTljMi0yZjk3MWMwZTIzNzA&ntb=1 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 https://www.bing.com/ck/a?!&&p=f6aaf34c9625aa63JmltdHM9MTY1ODczNDE2MiZpZ3VpZD1lMzMwMDM1NS03ZGFjLTQwZjktOGNjMS05MzBmMmNkZmM1ZWYmaW5zaWQ9NTE3MQ&ptn=3&hsh=3&fclid=80776687-0beb-11ed-beda-d2daf567e3ca&u=a1aHR0cHM6Ly9zdXBwb3J0Lm1pY3Jvc29mdC5jb20vZW4tdXMvb2ZmaWNlL2Zvcm1hdC1hbi1leGNlbC10YWJsZS02Nzg5NjE5Zi1jODg5LTQ5NWMtOTljMi0yZjk3MWMwZTIzNzA&ntb=1 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!
