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.