2

I'm trying to format table cells when the first row value and header column matches values from a named range in Google Sheets. I have solved it as a formula with TRUE and FALSE, but I can't adopt it for the conditional formatting.

Example Sheet: https://docs.google.com/spreadsheets/d/1MpViBGIOYoeMpB2zpb81hwlNOJcShP86OHYlI92mKGA/edit?usp=sharing

My source table: --> named ranges "mapping_name" and "mapping"

+-------+--------+---------+-----------+
|       |        |         |           |
+-------+--------+---------+-----------+
| name1 | Monday | Tuesday |           |
| name2 | Monday |         | Wednesday |
| name3 |        |         |           |
+-------+--------+---------+-----------+

My table to be formatted:

+-------+--------+---------+-----------+--+--+
|       | Monday | Tuesday | Wednesday |  |  |
+-------+--------+---------+-----------+--+--+
| name1 | True   | True    | True      |  |  |
| name2 | True   | True    | False     |  |  |
| name3 | False  | False   | False     |  |  |
+-------+--------+---------+-----------+--+--+

My formula looks like this

=OR(ARRAYFORMULA((B$34 = index(mapping;match($A35;mapping_name;0))) * ($A35 = index(mapping_name;match($A35;mapping_name;0)))))

B34 is the header with weekdays A35 is the name column

The formula works, but I can't translate it to a format that works for conditional formatting. Any help would be appreciated!

player0
  • 124,011
  • 12
  • 67
  • 124
Andii
  • 484
  • 1
  • 4
  • 19
  • You could make the true/false table invisible and with conditional format check if its true/false applying the rule to the table with the real values. – Gonzalo Feb 03 '20 at 09:39
  • Thanks, the true false labels are just there for reference, but in the real table there are various values in the cells. – Andii Feb 03 '20 at 11:45

2 Answers2

2

In your 'expected result' table, I used this formula for conditional formatting

=ISNUMBER(MATCH($A18&"_"&B$17, ArrayFormula(TRANSPOSE(SPLIT(TEXTJOIN("@", 1, $A$4:$A$6&"_"&$B$4:$D$6), "@"))), 0))

Or, if you want to used the named ranges, try

=ISNUMBER(MATCH($A18&"_"&B$17, ArrayFormula(TRANSPOSE(SPLIT(TEXTJOIN("@", 1, INDIRECT("mapping_name")&"_"&INDIRECT("mapping")), "@"))), 0))

(Note the use of quotation marks around the named ranges.)

See if that works for you?

JPV
  • 26,499
  • 4
  • 33
  • 48
1

try:

=MATCH(B4, $B$17:$D$17, 0)=COLUMN(A1)

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thank you, that worked for the specific test setup, but as I have the two tables separated over multiple sheets and named ranges the other solution was easier to implement. – Andii Feb 03 '20 at 11:45