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
draft sheet
draft sheet after adding a player
and the according formatting in players
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
- How do you add UI inside cells in a google spreadsheet using app script?
- How can I force data input into cells in Google Sheets to be in a desired date format?
- How do I find a partial match from a data table to an individual cell?
- Highlighting non-first instance duplicates of words and counting highlighted cells
- Schedule View: INDEX MATCH from table and return multiple cells into one
- How to Show/Hide rows in google sheets using a script?
- Popping a Modal Dialog when changing the drop down entry in Google Sheets
- Transfer background color from cell range to drop down options
- How can I match a smart chip using SUMIF?
- List Google drive folder contents to google sheets with only new files
- Trying to figure out why my GoogleSheets Date formula is not working
- Count if name matches and outcome is complete
- Google Sheets Row Number of last row in a defined table
- How can I use INDEX and MATCH along with conditions to filter data into different tables?
- Google Sheets formula to remove all punctuation excluding period (.)
- Formula to find missing serial numbers
- Google Sheet Vlookup Multiple Criteria
- Copy Data from one column and past at the end of another column
- Query formula not grouping properly
- Comparing text fields that contain dates in Google Sheets
- Signature Pad (Canvas) to Google Sheets
- How to use the cell number instead of its value in a Filter + Importrange
- regexreplace multiple columns for football data
- I want to use a macro to check a value in a sheet and return one of two values
- How can I remove consecutive duplicates from a range in Google Sheets?
- Using FILTER with multiple conditions on Google Sheet
- How to union ranges in google spreadsheets
- Google Sheets Dynamic Dropdown List Dependent with multiple rows
- Formula for filtering list based off of key with multiple criteria in Google Sheets
- Conditional Dropdowns in Google Sheets Based on Multiple Criteria