2

Below is a formula I want to use in conditional formatting. Its purpose is to blur a range in a text concatenation table if a checkbox B15 is unchecked and if the left and right of the string matches any value from 'Code Sheet'!X4:AD4 (m, ɱ, n, ɳ, ɲ, ŋ, or ɴ).

=iferror(and($B$15=false, if(match(left(C19,1),{'Code Sheet'!X4:AD4},0), if(match(right(C19,1),{'Code Sheet'!X4:AD4},0), true,false))),false)

The conditional formatting should blur any combo of the aforementioned values. But my formula does not work outside of a cell. The conditional formatting box turns red without feedback. I have no idea why. Where am I going wrong?

Note: the concatenation table has the range of C19:CH102, and the table filters and transposes values - meaning the formula must also adapt to changes in data position.

Improve this question

1 Answer 1

Reset to default
3

Use indirect() when referring to another sheet in a conditional formatting custom formula rule, like this:

=not($B$15) * ifna(match(left(C19), indirect("Code Sheet!X4:AD4"), 0)) * ifna(match(right(C19), indirect("Code Sheet!X4:AD4"), 0))

The formula will only return a truthy value when all three conditions are met. To use an "or" condition that returns a truthy value when any of the conditions is met, replace the * with +. To always require the first condition, but accept either of the two following conditions, use first * ( second + third ). See Boolean arithmetic.

Improve this answer
5
  • Your suggested formula seems to be multiplying values. When I tested it on a cell where the conditions would fail, 0 was the output. When I tested it on a cell where conditions were met, 6 was the output. Commented Jan 7, 2023 at 18:52
  • That is correct. Any non-zero number will be considered truthy, i.e., interpreted as true, in a conditional formatting custom formula rule. Commented Jan 7, 2023 at 19:08
  • Ok. But the conditional formatting box is still angry, saying "invalid formula". Commented Jan 7, 2023 at 19:11
  • Look on the sheet labeled "Phonotactics Calculator." docs.google.com/spreadsheets/d/… Commented Jan 7, 2023 at 19:16
  • Edited the answer. There are currently no values in your sample spreadsheet that would meet the conditions because the checkbox in B15 is ticked. Untick it to find a cluster of matching cells at V38:AB44. Commented Jan 7, 2023 at 19:35