Forum Discussion

Celia9's avatar
Celia9
Brass Contributor
Jul 24, 2022
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 na...
excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Jul 25, 2022

    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's avatar
Lorenzo
Silver Contributor
Jul 25, 2022

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

Celia9's avatar
Celia9
Brass Contributor
Jul 25, 2022
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!