Sample sheet at https://docs.google.com/spreadsheets/d/1ftwsBOKv9XsJzWckE06unsXMIvX4A4GmX-wQ-xGwVng/edit?usp=sharing
I'm trying to apply conditional formatting to the values in the "Actual versions" sheet, depending on if they appear in the relevant range on the "Allowed versions" sheet. I've included a manually formatted version to show what I'm trying to achieve.
Note - in the real thing there'll be several more rows and columns in both sheets.
I've tried several COUNTIF formulas but now I'm stuck. I wonder if I should be using MATCH or something else? Any help much appreciated
CodePudding user response：
In conditional formatting if you want to refer other sheet inside formula you have to use INDIRECT() function. Try below formula-
=ISNUMBER(MATCH(B2,INDEX(INDIRECT("'Allowed versions'!$B$2:$D$3"),MATCH(B$1,INDIRECT("'Allowed versions'!$A$2:$A$3"),0)),0))