Search code examples
google-sheetsconditional-formatting

How to conditionally format cells in one sheet based on values in another sheet in google sheets

I have two sheets in a google sheet named draft and players. Once a player is listed anywhere within B2:M21 I'd like to add conditional formatting with a strikethrough in the players sheet.

players sheet

enter image description here

draft sheet

enter image description here

draft sheet after adding a player

enter image description here

and the according formatting in players

enter image description here

I tried using =search() but couldn't quite figure out how to search for the player in each given row instead of one overall search term.

Solution

  • You can try by using XMATCH to find the name in the full range of the other sheet. For that you'll need to use INDIRECT (since Conditional Formatting doesn't allow by default to access other sheets) and TOCOL to make it a unique column:

    =XMATCH ($A2,TOCOL(INDIRECT("Draft!A:Z"),1))

    Adapt the range of the Draft sheet if needed