#### learning1984

##### New Member
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

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### learning1984

##### New Member
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

#### MisterProzilla

##### Active Member
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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,163
Messages
5,835,742
Members
430,384
Latest member
kiuwai

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back