Condition Formating, how to highlight a cell when

Good Day

I am wondering what custom formula i should use in condition formatting to highlight a cell when it doesn't include a "part name" i have in a list on sheet2

I want Cell A4:A1000 to turn orange when the text in the cell next to it "is not" on a list in Sheet 2 A2:A100

let me know what additional information you need... sorry

Im currently stuck with something like this....:
F4=indirect('Auto Fill'!A2:A343)
but i need it to not be on the list... which is something that's missing from this formula as well... so maybe:
F4= ISNOT indirect('Autofill'!A2:A343)

回答1:

This is the inverse of your request - I'll leave it as an exercise to the reader how one would get the opposite.

Basically, you want check if the value of some cell is in the list of valid values. If you were computing this as a sheet value, you would use =COUNTIF. For conditional formatting, the same applies:

=COUNTIF({LOOKUP_RANGE_ABS_REF}, {VAL_TO_CHECK})
=COUNTIF($D$2:$D, B2)

Here's the formula in action:

Note that T-3000 and wheels are not in the "Part List" column, and thus do not match.

Comparing to different worksheet within same workbook

If the data to be compared against is not in the same worksheet, the range reference {LOOKUP_RANGE_ABS_REF} must be wrapped in a call to INDIRECT(), i.e.

=COUNTIF(INDIRECT("{OTHER_WORKSHEET_NAME}!{LOOKUP_RANGE_ABS_REF}"), {VAL_TO_CHECK})
=COUNTIF(INDIRECT("some sheet name!$A$2:$A"), B2)

As per official documentation,

Formulas can only reference the same sheet, using standard notation "(='sheetname'!cell)." To reference another sheet in the formula, use the INDIRECT function.

This is also noted in several other SO questions (albeit using different core formulas besides COUNTIF):

https://stackoverflow.com/a/25753889
https://stackoverflow.com/a/28910087
https://stackoverflow.com/a/36684815
https://stackoverflow.com/a/37634170
(I'm sure there are more.)

Missing from those answers is the caveat that the indirection you've just added is not robust to changes to the value of {OTHER_WORKSHEET_NAME}, e.g. you (or some other editor) changed the actual name of the worksheet.

Unlike traditional entered-on-the-worksheet formulas, there is no "run-time" reference link that will update the static text value you had to enter when you created the Conditional Format rule. This lack of reference-updating is actually one of the useful features of INDIRECT(), so don't expect it to ever change.

Furhtermore, the reference break will not be immediately evident. Any items that were added to your formatted range after the name change had their format computed using the broken reference, but any items added before the name changed will keep their current format. Only when the actual range that is wrapped by INDIRECT is edited will any pre-existing conditional formats be recomputed. Thus, you may not notice that the sheet was renamed until you add a new valid part to the list.

回答2:

Quick kludge, import what you need (I gave it the name List) from sheet2 (you'll have to grant permission), Select ColumnA and apply a CF formula rule of:

=and(A1<>"",iserror(match(B1,IMPORTRANGE(" k e y ","List"),0)))