Suppose that the wordlist is in Column A of Sheet1, and you want to apply validation to the range B2:D10 of the current sheet.
Method 1: match
Use custom formula
=iserror(match(B2, Sheet1!$A$1:$A$6, 0))
for data validation.
- B2 is the upper left corner of the range to which the rule is applied.
- The third parameter 0 requires exact match
- The formula returns True only if
match returns an error, which means no match is found.
Data validation accepts the values for which the formula evaluates to True. Same can be done with conditional formatting.
Method 2: regexmatch
Form a regular expression from the list and validate against that. The string
"^(" & join("|", filter(Sheet1!$A1:$A, len(Sheet1!$A1:$A))) & ")$"
concatenates all values in that column into a regular expression that will match any of them. (For example, "^(first|second|third)$").
Then you can use regexmatch by this string to validate or conditionally format the data anywhere else. For example:
=not(regexmatch(B2, "^("&join("|", filter(Sheet1!$A1:$A, len(Sheet1!$A1:$A)))&")$"))
which means the content must not match the regular expression.
EXPLANATION OF BAD FORMULA
=MATCH(E6,indirect("'5 Star Gear Sets (Hidden)'!E6:E"),0) =MATCH(B6,indirect("'5 Star Gear Sets (Hidden)'!A6:A"),0)
The extra formula starting with a second = is the same as wrapping the entire formula with AND( , ).
=AND(MATCH(E6,indirect("'5 Star Gear Sets (Hidden)'!E6:E"),0),MATCH(B6,indirect("'5 Star Gear Sets (Hidden)'!A6:A"),0)
This Formula does not work because it checks to see if E6 matches any cell in E6 thru E of the referenced sheet and then also checks if B6 matches any cell in A6 thru A of the referenced sheet. Once it finds the first match in the reference sheet, it does NOT use the same row in the range of the second MATCH function.
=MATCH(search_key, range, [search_type])
Example:
=MATCH(A6,B1:B100,0)
Using MATCH in a Conditional Format will return True or False.
DISCLAIMER
I wrote the answer for a different sheet than the one listed in the original question. If using link provided in the question for the spreadsheet, the answer is meant to be applied to what would normally be considered Sheet1.
ANSWER
Apply to Range: E6:E
Format Cell if...: Custom Formula
Formula:
=AND(NOT(FILTER(INDIRECT("'6 Star Gear Sets (Hidden)'!D$6:D"),INDIRECT("'6 Star Gear Sets (Hidden)'!A$6:A")=B6)=E6),E6<>"")
Formating Colors
Text Color: None
Background Color: Light Red
=FILTER(range, condition1, [condition2], ...)
Example:
=FILTER(A1:A100,B1:B100=C1)
The FILTER( , ) function needs to be used instead of the MATCH( , , ) function. FILTER( , ) references the cell(s) from the range whos row(s) matches the condition(s).
=INDIRECT(cell_reference_as_string, [is_A1_notation])
Example:
=INDIRECT("Sheet2!A1:B")
The INDIRECT( ) function has to be used because the reference sheet can not be referenced directly.
=INDIRECT("'6 Star Gear Sets (Hidden)'!A6:A")=B6
=B6 has to be added to condition1 so that only the value in the range are referenced whos row matches the condition1. This finds which cell in range A6 thru A in the reference sheet matches B6. Only 1 cell is returned because A6 thru A in the reference sheet are unique.
=INDIRECT("'6 Star Gear Sets (Hidden)'!A$6:A")=B6
$ needs to be added in front of the row in both the range and condition in order for them NOT to change as the Conditional Format Rule goes down each cell to apply the condition to. (Something I failed to do in the original formula.)
=FILTER(INDIRECT("'6 Star Gear Sets (Hidden)'!D$6:D"),INDIRECT("'6 Star Gear Sets (Hidden)'!A$6:A")=B6)
Add the FILTER( , ) function and the range. '6 Star Gear Sets (Hidden)'!D$6:D returns the contents of D6 thru D of the referenced sheet that is on the same row as 6 Star Gear Sets (Hidden)'!A$6:A found in condition1.
=FILTER(INDIRECT("'6 Star Gear Sets (Hidden)'!D$6:D"),INDIRECT("'6 Star Gear Sets (Hidden)'!A$6:A")=B6)=E6
=E6 has to be added to the end of FILTER( , ) so that the formula checks to see if E6 matches the resulting contents of the cell found from D6 thru D of the referenced sheet. Because it is a Conditional Formating Rule, the result of the FILTER is either True or False, thus applying the Format if it is True and NOT applying the Format if it is False.
=NOT(FILTER(INDIRECT("'6 Star Gear Sets (Hidden)'!D$6:D"),INDIRECT("'6 Star Gear Sets (Hidden)'!A$6:A")=B6)=E6)
=NOT is added because we want True to actually be False and False to actually be True. We are trying to Format the cells that are not True of the FILTER( , ) we are using.
=AND(NOT(FILTER(INDIRECT("'6 Star Gear Sets (Hidden)'!D$6:D"),INDIRECT("'6 Star Gear Sets (Hidden)'!A$6:A")=B6)=E6),E6<>"")
=AND( , ) and E6<>"" has to be added so that it does NOT format every cell in column E that is blank. =AND( , ) in this case states that not only does the FILTER( , ) must be true, but also E6<>"". E6<>"" just states that E6 is not BLANK.
Best Answer