Conditional Formatting

[shunter6669]

Hi all. Im looking for some help with Formulas and conditional formatting. i have 1 work book with 2 sheets. i have information in column a and b of both sheets. i need the formatting to do a few things. the idea of the sheets is for sheet 1 to have information entered into it and sheet 2 is the "database" sheet.

When information is entered into sheet 1 it needs to be checked against sheet 2. the details in sheet 1 and and 2 will not always be in the same cells so it needs to be able to look for the details.

So far i have 2 formulas in conditional formatting working but the 3rd is a problem. The first one "=COUNTIFS('PC numbers'!$A:$A,A1,'PC numbers'!$B:$B,"Enabled")" is is taking the information in sheet 1 column a and finding the matching number in sheet 2 column a. it then looks at column b and if the status is enable it changes to one colour.

The second is "=COUNTIFS('PC numbers'!$A:$A,A1,'PC numbers'!$B:$B,"Deactivated")" and follows the same rules as 1 except changes to a diffrent colour if column b says deactivated.

The last thing i need is for the details entered into sheet 1 to to look at sheet 2 and if details of column b dont match change the colour of colum b on sheet 1. So far i have this "=INDEX(B:B,MATCH('PC numbers'!A1,A:A,0))='PC numbers'!B1" but it only works if the information is in the same cells on both sheets. how can i make this 3rd rule search for the information?

EG below

sheet 1

[TABLE="width: 500"]
<tbody>[TR]
[TD]12345[/TD]
[TD]enabled[/TD]
[/TR]
[TR]
[TD]6789[/TD]
[TD]deactivated[/TD]
[/TR]
[TR]
[TD]123336[/TD]
[TD]enabled[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2

[TABLE="width: 500"]
<tbody>[TR]
[TD]123336[/TD]
[TD]enabled[/TD]
[/TR]
[TR]
[TD]6789[/TD]
[TD]enabled[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]deactived[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[shunter6669]

anyone???

 

[FormR]

anyone???

Hi, it looks to me like you've got your sheet references mixed up. Is sheet2 called "PC Numbers"?

Does this work any better?

=INDEX('PC numbers'!B:B,MATCH(A1,'PC numbers'!A:A,0))=B1

 

[BGY23]

Hi,

I don't know why you're using Countifs? Is there more than one instance of the PC number on sheet 2?

Anyway assuming sheet 2 PC numbers are unique then


Conditional formating with the following rules in the order they're listed will do what you want.

It assumes your daya is in A1 on sheet 1. Please adjust as rquired.

It's not good practice to use A:A as a reference because then excel checks the whole column which in Excel 2010 is 1,048,576 cells. You should maybe do a range that will fit your needs, say up to 2,000 rows if thats the max your likely to have.

=VLOOKUP($A1,Sheet2!$A$1:$B$15,2,0)="enabled"
=VLOOKUP($A1,Sheet2!$A$1:$B$15,2,0)="deactivated"
=VLOOKUP($A1,Sheet2!$A$1:$B$15,2,0)<>$b1

Good luck

 

[shunter6669]

Hi, it looks to me like you've got your sheet references mixed up. Is sheet2 called "PC Numbers"?

Does this work any better?

=INDEX('PC numbers'!B:B,MATCH(A1,'PC numbers'!A:A,0))=B1

that did the trick. thank you very much