shunter6669
New Member
- Joined
- Apr 19, 2016
- Messages
- 3
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
<tbody>
</tbody>
Sheet 2
<tbody>
</tbody>
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
12345 | enabled |
6789 | deactivated |
123336 | enabled |
<tbody>
</tbody>
Sheet 2
123336 | enabled |
6789 | enabled |
12345 | deactived |
<tbody>
</tbody>