Google Sheets - Conditional Formatting based on column heading of cell value

1

I've had a problem that's been bugging me for days and I can't quite figure it out. Scenario:

  1. Names: This is an unsorted sheet where there are a bunch of names.
  2. Team: This is another unsorted sheet, but each name belongs to one of four teams indicated by a column and its header - Alpha, Beta, Charlie & Delta

I'm trying to come up with a conditional formatting formula for the Names sheet whereby it would, for example, colour names of people belonging to Alpha Team RED, Beta team GREEN, etc.

Example:

NAMES

Barry
Harrison
Oliver
Caitlin
Hartley

TEAM

ALPHA         BETA         CHARLIE         DELTA
Barry         Harrison     Hartley         Oliver
Caitlin

So in the above examples, in the Names sheet, Barry and Caitlin's names would be coloured red since they're in ALPHA team. Harrison would be green since he's in BETA team; and so forth.

Flynn

Posted 2016-06-17T04:27:28.140

Reputation: 200

Question was closed 2016-06-18T08:02:53.197

Please add some example data to your question. It's very difficult to understand it now. – Máté Juhász – 2016-06-17T04:45:21.123

@MátéJuhász Sorry, added some sample data and an explanations. Hope that makes it clearer – Flynn – 2016-06-17T06:38:46.203

What product do you use? Excel or Google? There are differences, so don't tag with both. You're wasting people's time. – teylyn – 2016-06-17T06:40:59.230

@teylyn My bad; fully aware they're different products but I've gotten away with using them interchangeably recently so my fault. Clarified it. – Flynn – 2016-06-17T06:57:29.537

Answers

1

In Excel you need four rules, one for each color.

enter image description here

The yellow rule is

=MATCH(A1,Teams!$A:$A,0)

The other ones use the columns B, C and D on the Teams sheet. In earlier versions of Excel, pre 2010, I think, you cannot reference ranges on other sheets in a CF formula. You can use named ranges instead, though.

teylyn

Posted 2016-06-17T04:27:28.140

Reputation: 19 551

Thanks. I tried using MATCH before but it didn't work as intended since I messed up trying to apply the formula to an entire range and typed it incorrectly.

Solved it with this and three other iterations with columns B, C and D

=MATCH(A1, INDIRECT("Divisions!$A:$A"), 0)

Thanks! – Flynn – 2016-06-17T07:16:17.187

I'm not sure why you would use Indirect() here. – teylyn – 2016-06-17T22:33:08.970

Asked: 2016-06-17T04:27:28.140

Viewed: 622 times

Active: 2016-06-17T06:53:40.580