Google spreadsheets: conditional formatting with custom formula based on values from different sheet

Question

I have a google spreadsheet with two sheets. On the first sheet, the items are listed in column A, and their types are in column B.

On the second sheet, I reference to the first sheet's column A with the formula: =UNIQUE(Sheet1!A:A).

Then I want to color the items in the 2nd sheet based on the item type (1st sheet's column B*). I try to add conditional formatting using the formula =Sheet1!$B:$B="Type1", but when I try to save rules, it says the formula is invalid.

What would be the correct formula for this case?

Answer 1

You cannot reference cells directly in the conditional format formula. Also, I do not think that formula would work.

First, you need the VLOOKUP function to get the object's type. Here's an example of using this function. The difference would be that the corresponding data in the first parameter would be preceded by the sheet name in your case.

See this image:

As you can see, VLOOKUP searchs for the item from D in the matrix A2:B4, and then returns the corresponding value (the value in the same row) in the second column of the matrix, which is column B.

So if you to apply this formula to conditional formatting, you would have:

=VLOOKUP($D$2:$D;$A$2:$B$4;2)="Dragon"

The first parameter of VLOOKUP has to be a range, and I started it from D2 so it would not match the header. In your case, it would become:

=VLOOKUP($D$2:$D;Sheet1!$A$2:$B$4;2)="Dragon"

But as you cannot use references directly, you need to use the INDIRECT function. It receives a cell range as a string and returns a reference to that range. It's the same as using references directly, but in this case you add an extra step (go figure). So the formula finally becomes:

=VLOOKUP($D:$D;INDIRECT("$A$2:$B$4");2)="Dragon"

You can choose any range to apply the format if you are using the new version of Spreadsheets. You could, for example, color the entire row or just a single cell.