HomeGoogle DocsSpreadsheetRole of Indirect Function in Conditional Formatting in Google Sheets

Role of Indirect Function in Conditional Formatting in Google Sheets

Published on

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 you can learn to use Indirect Function in Google Sheets Conditional Formatting.

The Purpose of Using 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 the Indirect Function.

In other words, you cannot directly refer to a cell in another sheet in the conditional formatting custom formula field.

For example, I have two sheets where the name of the first sheet is “Find Group” and the second sheet is “Student Group”.

My active sheet is “Find Group”. Normally, in formulas, we can refer to cell A1 in “Student Group” as below.

='Student Group'!A1

But you cannot use it as above in the custom formula field in conditional formatting. Instead, you should use it indirectly as below.

=indirect("Student Group!A1")

Let me give you one example.

Conditional Formatting Based on Cell Reference in Another Sheet in Google Sheets

I’ve already explained the purpose of using Indirect Function in conditional Formatting in Google Sheets. Now see a practical example to it.

I am pretty sure that you will like the following example. First, let me explain what I’m going to do.

I have a dataset containing student names in the sheet “Student Group” categorized under different groups.

The group names are RED, BLUE, GREEN and YELLOW as below.

Screenshot 1:

Sample Data - Indirect function in conditional formatting

Assume the students have participated in different competitions in a school annual day event.

I have the names of the winners in a sheet without the group name associated with it.

What I want to do is, in another sheet (here sheet name is “Find Group”), when I insert the name of any winners from any of the group, the cell to be highlighted with their group color as below.

Screenshot 2:

Refer another sheet to color cells using indirect

I will explain to you how we can do this and on the course, you will also learn the role/use of Indirect Function in Conditional Formatting in Google Sheets.

4 Formulas for 4 Colors and How-To Use It in Highlighting

Let’s see the conditional formatting rules for the above example.

We are going to apply the conditional formatting in A2:A range of the “Find Group” (refer to screenshot # 2). But the reference data is in B2:E range of the “Student Group” (refer to screenshot # 1).

There are four groups in four columns, so we require four custom formulas for the above purpose.

RED Group (Column B)

For the RED group in Column B, the normal formula (not for conditional formatting) that we can use in cell B2 in the “Find Group” or any cell other than cell A2 is as below.

=countif('Student Group'!B2:B,A2)

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 the Indirect function as below.

Formula # 1:

=countif(indirect("Student Group!B2:B"),A2)

I’ll explain in detail how to insert this indirect formula in conditional formatting in Google Sheets. But before that here are the other three formulas for the other three columns.

BLUE Group (Column C)

Formula # 2:

=countif(indirect("Student Group!C2:C"),A2)

GREEN Group (Column D)

Formula # 3:

=countif(indirect("Student Group!D2:D"),A2)

YELLOW Group (Column E)

Formula # 4:

=countif(indirect("Student Group!E2:E"),A2)

How to Use these Indirect Formulas in Conditional Formatting?

Please follow the below steps.

Make sure that the active cell is A2 in the sheet “Find Group”. Then go to the menu Format > Conditional formatting.

Type A2:A or A2:A100 (your choice of range) under the “Apply to range”. Then under the drop-down “Format cells if…” select “Custom formula is” and paste the formula # 1.

Under “Formatting style” select the color Red.

Screenshot 3:

Indirect conditional formatting rules in google sheets - Settings

Click “Add another rule” and copy the formula # 2 from above and paste it. Change the color to Blue. Similarly, add the other two formulas and set their colors.

Now test whether all the formulas are working as desired by inputting the name of a student in any cell from any group in the range A2:A in the sheet “Find Group”.

Never copy-paste names as it might clear the conditional formatting rules which we have set.

Here are two more tutorials in which I have used the Indirect Function in Conditional Formatting in Google Sheets.

Here is my sample Google Sheets file.

Indirect Formatting Eg

Thanks for the stay! Enjoy!

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

8 COMMENTS

  1. I am trying to shade some cells based on the numerical value in another cell.

    I have have tried many options, but it does not seem to work in Google Sheets.

    E.g., in cell A1 I put the value 6. So then I want this to shade the cell range A6:C6.

    …how simple is that, but I cannot achieve it.

    I have tried using the Indirect and Index functions in the conditional formatting, but it doesn’t work.

    Any ideas, please?

    • Hi, Giles,

      This formula will highlight A1:C1 if A1 is 1, A2:C2 if A1 is 2, and so on.

      =INDEX(
      LET(control_cell,A1,range,INDIRECT("A"&$A$1&":C"&$A$1),
      MATCH(CELL("address",control_cell),ADDRESS(ROW(range),COLUMN(range)),0))
      )

      Apply to range: A1:C1000.

  2. Goal:- Have the cell change color once the original number is replaced with a new number.

    Thanks for any potential help!

  3. I tried using your formula, but it does not apply the formatting correctly. Instead of formatting only the cells in the range that match the criteria, it formats the entire range if one cell matches. How do I get it to stop doing that?

  4. I can use a named range to refer to a cell in another sheet, right? I have tried this in the following formula =IF($A80,IF(AND(($M8+Last_contact_followup<=TODAY()),S8"Won"),TRUE,FALSE),"") and I get an error message if I put this formula into Conditional formatting / Custom formula in a Google Sheet.

    The error comes from the named range “Last_contact_followup” and I have no idea why? This works in Excel but not in Google Sheets. Where do I get wrong?

    • Hi, Thomas,

      I don’t know what you are trying to achieve. So it’s tough for me to correct the formula.

      I am sure that the formula is not coded correctly. For example, take a look at the AND use. I guess there is a small typo in your formula. It must be as below.

      =AND($M8+Last_contact_followup<=TODAY(),S8="Won")

      This part will work correctly. It would return TRUE or FALSE. But in conditional formatting use Indirect with the named ranges as below.

      =AND($M8+indirect("Last_contact_followup")<=TODAY(),S8="Won")

      It will highlight the "Apply to range" cell (see the conditional formatting panel) to your set color if the output is TRUE, else blank. I don't know why you are using the IF statement. It may not be required in conditional formatting.

      Best,

LEAVE A REPLY

Please enter your comment!
Please enter your name here