Relative reference in conditional formatting is not a matter of concern in Google Sheets if the cell-range to highlight and the conditions are within the same sheet. It’s a concern if it’s in two different sheets in the same file!
Unlike normal worksheet formulas, the formulas in conditional formatting, known as formula rules, require the INDIRECT function to refer to another sheet.
As you may know, the Indirect function uses cell reference as a string. So when we use Indirect to refer to a cell in Google Sheets, in ‘normal’ way, relative cell reference has no effect.
Syntax: INDIRECT(cell_reference_as_string, [is_A1_notation])But we can overcome that by using the ROW and COLUMN functions within the ADDRESS function as the cell_reference_as_string argument in Indirect.
In this post let’s learn how to apply relative reference in conditional formatting when two sheets in the same file involved in Google Sheets.
Before that, you must know how to apply relative cell reference in conditional formatting within the same sheet in Google Sheets.
Relative Cell Reference in Conditional Formatting in the Same Sheet
The relative cell reference helps us to use a single conditional format formula to an entire row or column range. So no need for you to create formula rules for each cell or row/column.
Let me start with a column range as it’s most common conditional formatting.
Formula for a Column Range
Here is one example. I want to highlight values in A2:A if B2:B is “Pending”. Both the values are in the same sheet. How to do that?
Here is the required formula which you must insert in the menu Format > Conditional formatting > Format rules. There select “Custom formula is” and insert the below formula.
Custom Formula # 1:
=B2="Pending"
The only thing you need to take care of is the “Apply to range” field inside the “Conditional format rule” panel.
There you must insert A2:A so that the above formula for the first cell in the range will be automatically applied to the selected range. Because we have used relative cell reference in the conditional format formula above (no dollar sign with the cell reference B2).
Suppose, I want to highlight both columns A and B. Changing the “Apply to range” to A2:B won’t yield the desired result.
In such a scenario, make the column (letter) absolute and the row (number) relative in the above formula.
Custom Formula # 2:
=$B2="Pending"
What about if the values are in two rows instead of two columns?
Formula for a Row Range
Here also there are no changes in the use of relative reference in conditional formatting in Google Sheets.
If the invoice numbers are in row # 1 and status are in row # 2, then the same formula rule # 1 is applicable to highlight row # 1.
Just change the “Apply to range” to B1:K1 to get the highlighting as per the image below.
To highlight both the rows, then using custom formula # 2 won’t work because there we have made the column absolute.
Here instead of the column (letter), we must make the row (number) absolute. Also change the range to highlight to A1:K2 from A1:K1.
Custom Formula # 3:
=B$2="Pending"
Relative Cell Reference in Conditional Formatting in Two Sheet Tabs
The above custom formulas won’t work if the invoice numbers are in one sheet and the statuses are in another sheet. No matter whether it’s column-wise or row-wise.
For example, the invoice numbers are in A1:A in “Sheet1”. But the status column B is in “Sheet2” B1:B.
That means the cell-range to highlight is in one sheet and the conditions (criteria) are in another sheet. In this situation, some of you may think that the below rule would work.
=Sheet2!B2="Pending"It won’t work as “Conditional format rule” in Google Sheets is only for the same sheet. So the workaround is the Indirect reference.
That means when there are two or more sheets in the same file involved in conditional formatting, we must use the Indirect function.
Must Read: Role of Indirect Function in Conditional Formatting in Google Sheets.
Since our topic is the relative reference in conditional formatting let us see how to use Indirect in such a scenario.
Indirect Formula for a Column Range
In the below image you can see the values (invoice numbers) to highlight in “Sheet1” and the criteria in “Sheet2”.
How to refer to “Sheet2” and highlight “Sheet1”?
Here is the formula to use.
Custom Formula # 4:
=indirect("'Sheet2'!"&address(row(B2),column(B2),4))="Pending"To apply relative cell reference highlighting to an entire row or we can say column A and B in “Sheet1”, just change B2 to $B2 in both the Row and Column functions within the Address.
Custom Formula # 5:
=indirect("'Sheet2'!"&address(row($B2),column($B2),3))="Pending"Don’t forget to change “Apply to range” to A2:B.
Indirect Formula for a Row Range
To learn this type of highlighting let’s consider that the values to highlight are in A1:K1 in “Sheet1” and the conditions are in A2:K2 in “Sheet2”.
If so, use the same above formula (custom formula # 4). Do not forget to set the “Apply to range” to B1:K1.
To highlight multi-rows in “Sheet1”, for example, cell-range A1:K2, based on the same above conditions in “Sheet2”, use the formula as below.
Custom Formula # 6:
=indirect("'Sheet2'!"&address(row(B$2),column(B$2),4))="Pending"That’s all about the relative reference in conditional formatting in Google Sheets. Thanks for the stay. Enjoy!
Additional Resources
