Forum Discussion
Conditional formating with multi lookup values
Hello everyone, I have a calendar and a table of employees with dates. I need when I enter the emp ID in cell AX5, the dates in the calendar that matche the days between the starting and ending...
See the attached workbook.
According to this article, you need to use the INDIRECT function to be able to refer to an Excel table in a Conditional Formatting formula. Here is such a formula:
=IF( COUNTIFS( INDIRECT("Table1[emp ID]"), $AY$3, INDIRECT("Table1[start date]"), "<="&AX8, INDIRECT("Table1[end date]"), ">="&AX8) > 0, TRUE, FALSE )(Most spaces are just for readability. Strictly speaking, the IF function surrounding the COUNTIFS function is not necessary, because of how Excel converts numbers to Booleans, but it makes the intent clearer.)
- Thank you very much, you really helped me a lot, Im working on a commercial project and I need this line of code in my project, may I use it for a commercial projects?
- << …may I use it for a commercial projects? >>
Of course. I make no copyright claim to anything I post to the community, nor have I seen such a claim from other contributors. For longer works, attribution would be appreciated, but is not required.
