Conditional formatting custom formula that can compare a string referenced on another sheet

Question

I am trying to indirectly reference another sheet within my Google sheets document in order to compare a string and color the cell accordingly if the text matches. Let's say that Sheet 1 is a list of employees with defining characteristics about that person, and Sheet 2 is another sheet where I reference that employee's name and want to format the cell based on their characteristics.

Like so:

Sheet 1:

  A    B
1 Jim  Man
2 Kate Woman

Sheet 2:

  A    B    C
4           Jim
5
6           Kate

When I reference that person in another sheet, like Sheet 2 cell C4, I want it to automatically be shaded a specific color if Jim is a man.

Then I would want to have cell C6 shaded a different color if Kate is a woman (different formatting rule, I would think, using the same structure but matching different text.)

I tried using this formula applied to cell C4:

=B1>INDIRECT("Sheet2!B2")="Man"

Should I be using ref_text to compare the indirect value to "Man"? Or should I be using ref_style and checking if the return is true or false?

I've never referenced another sheet in a conditional formatting rule before so appreciate any help in advance.

Tedinoz · Accepted Answer · 2025-05-18 04:22:00Z

1

Select the data range on Sheet2 (for example, select the entire column by clicking in the "C" header cell)
Select Format, Conditional formatting
Add a rule
- Custom formula
  - ="Man"=vlookup(C1,indirect("Sheet1!$A$1:$B$2"),2,)
  - Select the cell background colour
  - Click OK
Add another rule
- Custom formula
- ="Woman"=vlookup(C1,indirect("Sheet1!$A$1:$B$2"),2,)
  - Select the cell background colour
  - Click OK

edited May 18 at 4:22

answered May 15 at 13:47

Tedinoz

8,3943 gold badges30 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bricks96 May 15 at 23:42

I'll throw in the suggestion to swap the sides of your equation to be ="Woman"=vlookup(C1,indirect("Sheet1!$A$1:$B$2"),2,) so that you can see which equation applies to which gender in the Conditional Formatting sidebar.

quesans · Accepted Answer · 2025-05-17 04:02:33Z

-1

=IFERROR(INDEX(Sheet1!B:B, MATCH(C4, Sheet1!A:A, 0)) = "Woman", FALSE)

=IFERROR(INDEX(Sheet1!B:B, MATCH(C4, Sheet1!A:A, 0)) = "Man", FALSE)

it will give you a correct data

edited May 17 at 4:02

answered May 15 at 7:33

quesans

12 bronze badges

2 Comments

Jeremy Caney May 16 at 22:00

Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. Would you kindly edit your answer to include additional details for the benefit of the community?

Jeremy Caney May 16 at 22:00

Also, while you’re at it, please revisit your formatting. The StackOverflow post authoring controls give you that ability—or you can you any standard Markdown capabilities.

Collectives™ on Stack Overflow

Conditional formatting custom formula that can compare a string referenced on another sheet

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related