I want to highlight a cell in a column with a specific color if it contains any of the elements from a range of cells on another sheet.
I believe I need to use 'custom formula is' with the
INDIRECT function to reference the range of cells, but cannot seem to produce a working formula that is considered valid for conditional formatting.
For example, I want the cell to be highlighted in red if it contains the name of a "past member", where past members are defined on another sheet in the range 'Past Members'!D5:D18
Could you please help me understand what kind of a custom formula could achieve this?