Do you know the role of Indirect function in Conditional Formatting in Google Sheets? If not follow this tutorial. Here you can find a very useful conditional formatting example and also learn to use Indirect Function in Google Sheets Conditional Formatting.
The Role of Indirect Function in Conditional Formatting in Google Sheets
When you have multiple sheets, and you want to format a sheet based on a cell reference in another sheet, you should use Indirect Function. In other words you cannot directly refer to a cell in another sheet in conditional formatting custom formula.
For example I have two sheets where Sheet 1 name is “Find Group” and Sheet 2 name is “Student Group”.
My active sheet is “Find Group”. Normally we can refer to cell A1 in “Student Group” as below.
But you cannot use it in Conditional Formatting Custom Formula. Instead you should use it indirectly as below.
By saying this I am taking you to an awesome Conditional formatting tutorial in Google Sheets.
Conditional Formatting Based on Cell Reference in Another Sheet in Google Sheets
I’ve already explained you why Indirect Function in Conditional Formatting in Google Sheets is required. Now see a practical example to it.
I am pretty sure that you will like the following tutorial. First let me explain what I’m going to do.
I have a list of students’ name in a sheet “Student Group” categorised under different groups. The group names are Red group, Blue group, Green group and Yellow group as below.
Imagine they have participated in different competitions in an school annual day event. I have the names of the winners in a sheet that without group name. What I want to do is, in another sheet (here sheet name is “Find Group”), when I type the name of any winners or anyone in the group, the cell should be highlighted with their group colour as below.
I will explain you how we can do this and you will also learn the use of Indirect Function in Conditional Formatting in Google Sheets.
Formula and Example for the Use of Indirect Function in Conditional Formatting
Let’s see the conditional formatting rules for the above example.
As already mentioned there are two sheets – “Find Group” and “Student Group”. We are going to apply the conditional formatting in A2:A range of “Find Group” (refer the screenshot 2). But the reference data is in B2:E range of “Student Group” (refer screenshot 1).
There are four groups in four columns, so we require four custom formulas for the above purpose. For Red group in Column B, the normal formula in “Find Group” is,
This Count formula would check the range B2:B in the sheet “Student group” for the criterion in A2 in “Find Group” and return 1, if matching found. The same formula we can use in conditional formatting but only with Indirect as below.
Formula # 1
For other Columns;
Formula # 2 to 4
for Column C
for Column D
for Column E
How to apply these formulas in Google Sheets Conditional Formatting.
Make sure that the active cell is A2 in the sheet “Find group”. Then go to the menu Format > Conditional formatting.
The “Apply to range” is A2:A, then under the drop down “Format cells if…” select “Custom formula is” and paste the first formula given above. Select the colour Red and “Done”. Click “Add another rule” follow the above steps to add other formulas. Choose the appropriate colours.
Now test whether all the formulas are working as desired by inputting names of students in any cell in the range A2:A in the sheet “Find Group”. Never copy paste names as it will clear the conditional formatting rules which we have set.
Here is my Google Sheets, where I made the above experiments.