Screenshot Reference:
Download the actual excel [86kb]
The Purpose: To use a conditional formatting formula to highlight which columns O through V a user must input based on their input in column N. Once a user provides input, the highlight should disappear.
The Problem: I have written and tested two formulas, both perform as expected in isolation, but when combined using the AND() function to achieve the above stated purpose the result is that no formatting is applied to the cells.
FYI: the header values in green are all named ranges which are lists of the tasks that users choose in "N". Also the formulas below should be applied as conditional formatting to the columns "O" to "V"
Formula 1: finds which column the header "work" is in ("N"), and then does a vlookup with the value of "N" for the given row ("Page Launch - WW"), in the named range found in row1 of the given column. It returns TRUE if it finds a match. =NOT(ISNA(VLOOKUP(INDIRECT(ADDRESS(ROW(),MATCH("work",$1:$1,0),1,0,),0),INDIRECT(INDIRECT(ADDRESS(1,COLUMN(),1,0,),0)),1,0))) -- works
Formula 2: checks the values of it's own cell. If the value is "" it returns TRUE.
=INDIRECT(ADDRESS(ROW(),COLUMN(),1,0),0)="" -- works
Combined Formula: uses AND() to check if the value for both 1 & 2 is TRUE or FALSE. It returns TRUE if both 1 & 2 are TRUE.
=AND((NOT(ISNA(VLOOKUP(INDIRECT(ADDRESS(ROW(),MATCH("work",$1:$1,0),1,0,),0),INDIRECT(INDIRECT(ADDRESS(1,COLUMN(),1,0,),0)),1,0)))),(INDIRECT(ADDRESS(ROW(),COLUMN(),1,0),0)="")) -- not working
