Having some problems with conditional formatting in Google Sheets

Question

So I have two sheets together and I'm trying to change the colour of a cell in one sheet based on if a range in the other sheet contains a certain value.

For example, in sheet 1 I would like cell A1 to turn red if the word "cat" occurs in a cell in the range A1:A20 in sheet 2.

I feel like this should be possible, but I'm not certain. Thanks for any help.

Edit: I was able to get the sheet1 cell to turn colour if a specific cell in sheet2 has the right word using =INDIRECT("Sheet2!A3")="cat". I tried changing the A3 to A1:A20 to search that range for the word cat but it didn't work.

Answer 1

You can format cells depending on whether the range Sheet2!A1:A20 contains cat with a conditional formatting custom formula rule like this:

=counta( iferror( filter( indirect("Sheet2!A1:A20"), indirect("Sheet2!A1:A20") = "cat") ) )