advanced conditional formatting??

[learning1984]

Hi all. im rather stuck.
I have a spreadsheet which has coloumns as follows in sheet 1
Col A ColB ColC ColD
Dept intrusive Y/N Work to do name
Eng Y remove oven grids Steve
Eng Y Re-lay cooler Dave
hyg Y Clear out Lidder Paige
prod N Clear Mixer Dan

I then have another sheet (sheet 2) which shows all equipment that needs to be checked when work recommences.
in this sheet column A contains all equipment ie Oven, Cooler, Mixer, Lidder, Conveyor 1, Conveyor 2, Moulders.

What I want is, if the work done in sheet 1 is intrusive (Y) the work column is searched to see if it contains the equipment, ie remove oven grids contains the equipment oven. I want the row in sheet2 containing the word oven to be highlighted if there is intrusive work being carried out in the oven, and etc for other work that contains words in col A in sheet 2

sheet 2 contains 8 columns, info in the rest of this sheet is irrelevant, but want all 8 cells in the row to be highlighted.

is this possible?

thanks so much

 

[learning1984]

Im unsure if i'd be using a macro, or something along the lines of =SEARCH formula to look at the equipment word in column A sheet 2, and try and find that word in column C (work to do) in sheet 1, as well as then making sure a Y is in column =B in sheet one

please help

 

[MisterProzilla]

Hi learning1984,

I think this is what you're after:

=COUNTIFS(Sheet1!$B$2:$B$5,"Y",Sheet1!$C$2:$C$5,"*"&$A2&"*")>0

Add that as a conditional formatting rule in sheet 2 and apply it to the range you want formatted. You'll need to edit the ranges it's looking for in Sheet1 to match the size of your actual table.