Highlighting Values in Sheet2 that Match Values in Sheet1 Conditionally

GiGiKing·March 19, 2024·0 comments

Do you want to learn how to conditionally highlight values in Google Sheets? In this tutorial, I’ll show you how to highlight values in Sheet2 that match values in Sheet1 conditionally. This isn’t just about highlighting duplicates – it involves a specific condition. So, let’s get started!

The Setup

First, let’s take a look at the data in our sheets. In “Sheet1”, we have a list of names in column A and their corresponding visa expiry dates in column B. In “Sheet2”, we want to highlight the names if they meet two criteria:

  1. The name must appear in column A in “Sheet1”.
  2. The visa expiry date in column B against the name must be less than or equal to today’s date.

Now that we know what we’re working with, let’s move on to the next steps.

Highlighting Values

To highlight the matching values in “Sheet2”, we can use the COUNTIFS function along with the INDIRECT function to refer to “Sheet1”. Here’s the formula you can use:

=countifs(indirect("Sheet1!$A$2:$A"),$A2:$A,indirect("Sheet1!$B$2:$B"),"<="&today())

This formula will count the number of matching values based on our criteria. If the count is greater than 0, the name in “Sheet2” will be highlighted.

Applying Formatting Rules

To apply the formatting rules in Google Sheets, follow these steps:

  1. Click on the “Format” menu.
  2. Select “Conditional formatting”.
  3. In the conditional formatting panel, set the range to A2:A in “Sheet2”.
  4. Choose “Custom formula is” as the format rule.
  5. Enter the formula mentioned above.

By following these steps, you’ll be able to highlight the names in “Sheet2” that match the criteria specified.

Another Approach: Using Drop-Down Menu

If you prefer a different approach, you can create a drop-down menu in cell A2 of “Sheet2” to check if a name exists in the list in “Sheet1”. Here’s how:

  1. Go to the “Data” menu.
  2. Select “Data Validation”.
  3. Set the “Criteria” as “List from Range”.
  4. Enter the range “Sheet1!A2:A” in the given field.
  5. Click “Save”.

This drop-down menu will show the names from “Sheet1”, and you can easily check the visa status of each employee.

Conclusion

Now you know how to highlight values in Sheet2 that match values in Sheet1 conditionally. These techniques can be valuable for managing and analyzing data in Google Sheets. If you want to learn more about conditional formatting, check out the additional resources below.

Remember, Crawlan.com is your go-to resource for all things Google Sheets. Visit our website for more tutorials, tips, and tricks that will take your Sheets skills to the next level.

Additional Resources:

GiGiKing

Related posts