Reuire Conditional Formatting to find Errors between columns

[Kmartin83]

Hi there,

I need a means of finding errors between two (or more) columns.

My first issue is that in Column F (titled, PREGNANT) I have 'Yes' or 'No' answers. I need to cross-examine this to Column E (titled, GENDER) where the answers are 'Female' or 'Male'. I'd like a forumla that if there is a 'Yes' in the pregnant column and 'Male' in the gender columns; the columns turn a specified colour. I hope this makes sense?

Secondly, I then need a means of cross-examining three columns (not all adjacent to each other). I'd need to be able to compare Column AF (titled, Drug_2) with column AO (titled, Days_last_month_alcohol_consumed) and column AP (titled, Units_per_day_last_month). Here, what I'd need is for three columns to turn a specified colour if the words 'NO SECOND DRUG' appear in column AF, and a numeric value greater than 14 appears in column AO AND a numeric value greater than 8 appears in column AP. In layman's terms, I'm error checking that someone hasn't listed a client as drug free if they are drinking 14 days or more in a month, consuming more than 8 units of alcohol on those days.

I hope someone out there can help me!!!

Thanks in advance,
Kelly M

[hemesh]

go to conditional formatting
assuming your data starts from E2
select new rule ---> select determine a formula for CF
type =AND($E2="male",$F2="Yes") then in format section choose colour and in applies to section select E2:F500 and select apply for first condition

[Kmartin83]

Thank you! This was a big help - could I possibly do this for the other columns that I listed in part two of my problem?? Not sure how to add in the third column etc... sorry I am very new to all this!

[hemesh]

for second condition
select new rule ---> select determine a formula for CF
=AND($AF2="No Second Drug",$AO2>=14,$AP2>8) then in format section choose colour and in applies to section type =$AF$2:$AF$400,$AO$2:$AP$400 and select apply for first condition

if your days in AO2 and AP2 is >=14 or >=8 then use below version
=AND($AF2="No Second Drug",$AO2>=14,$AP2>=8)


if this is helpful click"*" add rep icon in the bottom left corner of my post

[Kmartin83]

Hmm it appears I'm having a problem with the formatting - it's only picking up one entry... I don't see the option to apply to section type??

[hemesh]

select Af2, go to conditional formatting again , see manage rules
There you will find applies to

[Kmartin83]

When I run the formatting for the pregnancy, it's only picking up one correctly... if I play around with it and select M / Pregnant the columns above are highlighted, not the ones I need??

[hemesh]

Hello Martin !

find attached

check CF---> Manage Rules-----> at the top select this worksheet
see CF formula by selecting edit formula and check the applies to


Or attach your book ! Go to advanced ---> select paper clip icon or select manage attachment

[Kmartin83]

You've been amazing help - absolutey!!! Thanks for everything!

[Kmartin83]

Hemesh, I have an additional problem (sorry to be cheeky to ask!)

I need to check that if in Column S someone has input "NFA", that "NFA" appears in column T as well - and if it doesn't or someone has entered something different (e.g, No Housing Problems) that this flags as a coloured cell too...

Any thoughts?
Thanks

[hemesh]

Try this Martin
=AND($S2="NFA",$S2<>$T2) select colour & then in applies to S2:T400

[Kmartin83]

Hiya,

Sorry I don't think I made myself clear on the additional request - my fault...

If someone inputs 'NFA' in column S - the ONLY appropriate/legal answer for Column T is 'NFA'.

For example, if in S2 I have ''NFA'' and in T2 I have ''NFA'' - the columns shouldn't change colour
But if I have ''NFA'' in S2 and ''Supported Housing'' in T2 it should change to the desired colour.

[hemesh]

its working in my sheet

[Kmartin83]

hmmm ... Let me have another go - it was highlighting both columns if they had NFA in each (which I don't need), but it's probably my user error!

[Kmartin83]

This isn't working for me for some reason :/

[hemesh]

attach your book ! Go to advanced ---> select paper clip icon or select manage attachment

[Kmartin83]

Heya,

I tried just before you responded - but as I am at work, we are blocked to uploading things to the internet (harsh internet restrictions)

Is there any other way I could get the file to you?

Thanks,

[Kmartin83]

I've gotten it to work, but it highlights everything with NFA in either column S or T.

Basically, the columns need to be highlighted if there is NFA in column S and either 'Hostel', 'Living with parents', Owned Property', 'Rented', 'Supported Housing' or 'Temporary' in column T.

Having NFA in both columns is a valid combination and therefore doesn't need to be highlighted.

I can work around this by layering rules for each label (Hostel, Living with parents etc) but I'm thinking there must be an easier way?

Sorry to be such a noob!