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

i have google spreadsheet two sheets. on first sheet, items listed in column a, , types in column b.

on second sheet, reference first sheet's column a formula: =unique(sheet1!a:a).

then want color items in 2nd sheet based on item type (1st sheet's column b*). try add conditional formatting using formula =sheet1!$b:$b="type1", when try save rules, says formula invalid.

what correct formula case?

you cannot reference cells directly in conditional format formula. also, not think formula work.

first, need vlookup function object's type. here's example of using function. difference corresponding data in first parameter preceded sheet name in case.

see image:

as can see, vlookup searchs item d in matrix a2:b4, , returns corresponding value (the value in same row) in second column of matrix, column b.

so if apply formula conditional formatting, have:

=vlookup($d$2:$d;$a$2:$b$4;2)="dragon"

the first parameter of vlookup has range, , started d2 not match header. in case, become:

=vlookup($d$2:$d;sheet1!$a$2:$b$4;2)="dragon"

but cannot use references directly, need use indirect function. receives cell range string , returns reference range. it's same using references directly, in case add step (go figure). formula becomes:

=vlookup($d:$d;indirect("$a$2:$b$4");2)="dragon"

you can choose range apply format if using new version of spreadsheets. could, example, color entire row or single cell.