I have two tabs in a Google Sheets file. One contains data to display (auto-generated), and another contains information about availability. They appear as such, where the sheets correspond 1-to-1:
User tally
A B C D
+----------+------+------+------+
1 | | OS 1 | OS 2 | OS 3 |
+----------+------+------+------+
2 | Device A | 12 | 0 | 512 |
+----------+------+------+------+
3 | Device B | 0 | 156 | 18 |
+----------+------+------+------+
4 | Device C | 0 | 0 | 0 |
+----------+------+------+------+
OS availability
A B C D
+----------+------+------+------+
1 | | OS 1 | OS 2 | OS 3 |
+----------+------+------+------+
2 | Device A | 1 | 0 | 1 |
+----------+------+------+------+
3 | Device B | 0 | 1 | 1 |
+----------+------+------+------+
4 | Device C | 0 | 0 | 1 |
+----------+------+------+------+
As you can see, the Devices for which an OS is not supported all have 0 users, as expected. However, there are some Devices that do support an OS, but 0 users have that combination (i.e. Device B on OS 3, and Device C entirely).
In my User tally spreadsheet, I want to use Conditional Formatting to change the background and text color of unsupported combinations to the same thing, making it appear as if the cell is completely blank. However, I want supported combinations to display their 0, indicating that users can use this combination, but don't for some reason.
I tried placing the following into the Conditional Formatting panel for 'User tally'!B2:D4 as a Custom formula is (hoping B2 would change per-cell like it does when you paste a formula across many cells), but it didn't appear to have an effect:
EQUAL('OS support'!B2, '0')
How do I use Conditional Formatting to change the color of each cell based on counterpart data from another table?
