advanced conditional formatting??

learning1984

New Member
Joined
Mar 2, 2016
Messages
14
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
Joined
Mar 2, 2016
Messages
14
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

Active Member
Joined
Nov 12, 2015
Messages
263
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.
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top