Did you know that the Indirect function plays a crucial role in conditional formatting in Google Sheets? If not, buckle up because we’re about to dive into this exciting tutorial. Here, you’ll not only find a useful example of conditional formatting but also learn how to harness the power of the Indirect function in Google Sheets.
Unleashing the Power of the Indirect Function
Imagine you have multiple sheets, and you want to format one sheet based on a cell reference in another. This is where the Indirect function comes into play. You see, you can’t directly refer to a cell in another sheet within the conditional formatting custom formula field.
Let’s say we have two sheets: “Find Group” and “Student Group.” By default, you could refer to cell A1 in the “Student Group” sheet using the formula: =’Student Group’!A1. However, in the custom formula field of conditional formatting, you need to use the Indirect function indirectly, like this: =indirect(“Student Group!A1”).
Allow me to demonstrate with an example.
Highlighting Based on Cell Reference in Another Sheet
In this practical example, we’ll work with a dataset containing student names categorized under different groups in the “Student Group” sheet. The group names are RED, BLUE, GREEN, and YELLOW.
Now, let’s say we have a separate sheet called “Find Group.” Whenever we enter the name of a winner from any group in this sheet, we want the cell to be highlighted with their respective group color.
Sounds intriguing, right? Let’s dive in and explore how to achieve this, while also unraveling the fascinating world of the Indirect function in conditional formatting.
Four Formulas and How to Use Them
For our example, we’ll be applying conditional formatting in the A2:A range of the “Find Group” sheet, with the reference data located in the B2:E range of the “Student Group” sheet.
Here are the four custom formulas we’ll use, one for each group:
RED Group (Column B)
In cell B2 (or any other cell besides A2), we can use the following formula to check for matching names within the “Student Group” sheet: =countif(‘Student Group’!B2:B,A2).
For conditional formatting, we need to modify the formula with the Indirect function, like this: =countif(indirect(“Student Group!B2:B”),A2).
Repeat the steps below for the remaining groups.
BLUE Group (Column C)
Formula: =countif(indirect(“Student Group!C2:C”),A2).
GREEN Group (Column D)
Formula: =countif(indirect(“Student Group!D2:D”),A2).
YELLOW Group (Column E)
Formula: =countif(indirect(“Student Group!E2:E”),A2).
Applying Conditional Formatting with Indirect Formulas
Now, let’s apply the conditional formatting using the Indirect formulas we just created. Follow these steps:
- Make sure the active cell is A2 in the “Find Group” sheet.
- Go to the Format menu and select Conditional formatting.
- In the “Apply to range” field, type A2:A or A2:A100 (or any specific range of your choice).
- Under the “Format cells if…” drop-down menu, select “Custom formula is” and paste the formula #1.
- Choose the color Red under “Formatting style”.
- Click “Add another rule,” and paste formula #2. Set the color to Blue.
- Repeat the process for the remaining formulas and set their respective colors.
- Test the formulas by entering a student’s name from any group into any cell within the A2:A range in the “Find Group” sheet.
Remember not to copy-paste names, as it may remove the conditional formatting rules we’ve set.
You can also find additional tutorials where I’ve utilized the Indirect function in conditional formatting within Google Sheets, such as:
- Highlight Cells if Same Cells in Another Sheet Have Values – Docs Sheets
- How to Conditional Format Duplicates Across Sheet Tabs in Google Sheets
To get a better understanding of the concept, feel free to refer to my sample Google Sheets file, “Indirect Formatting Eg”.
With that, you’ve unlocked the secret to using the Indirect function in conditional formatting! Enjoy experimenting with this powerful tool and discover new ways to make your Google Sheets work for you.
Thanks for joining me on this journey. Until next time, happy formatting!
