Conditional Formatting with Vlookup and IF Funtions over two Tables

[mk90]

Hi,

What I am trying to achieve is, when a line in table 2 has
=IF(AND(Orders[Post Pre]="M",Orders[Supplier]="AWS - EXT",Orders[Status]="Completed")
to then look up the Job# in B and find that number in Table 1 and Colour a specific cell (P in table 1, to be linked to Orders[Supplier]="AWS - EXT")

Do I need to use Vlookup after IF(AND), or do I need to use Index/Match?
either of these I cant seem to figure out with conditional formatting.

Thanks
(sorry if this is so confusing!)


See below table photos, I hope it paints the picture of what I would like to achieve...
Sheet 2)

Sheet 1)

 

[jtakw]

Hi,

You circled Column J & "M", is that Also a requirement?
And for your sample, you want "Y" in P8 highlighted?

 

[jtakw]

Use one of these for your CF formula.

Q21 includes the J Column requirement for M
R21 excludes J Column
Q21:R23 shown for reference purposes Only, you Don't need them.

Change/Adjust Cell Range/References to suit your data:

Book3.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
5	83000
6	83000		XYZ					YES
7	82000		ABC					Completed
8	81000
9	81000
10	81000		AWS - EXT					Completed	M
11	83000
12	83000
13	83000
14	83000		DEF					Completed
15	83000
16	83000
17
18
19
20
21	83000														Y	0	0
22	82000														Y	0	0
23	81000														Y	1	1
24																With J	Without J
Sheet901

Cell Formulas
Range	Formula
Q21:Q23	Q21	=COUNTIFS(B$5:B$20,B21,D$5:D$20,"AWS - EXT",I$5:I$20,"Completed",J$5:J$20,"M")
R21:R23	R21	=COUNTIFS(B$5:B$20,B21,D$5:D$20,"AWS - EXT",I$5:I$20,"Completed")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P21:P23	Expression	=COUNTIFS(B$5:B$20,B21,D$5:D$20,"AWS - EXT",I$5:I$20,"Completed",J$5:J$20,"M")	text	NO

 

[mk90]

Thank you for your fast reply.

I see what you have done, although I cant get it to work over 2 sheets.

 

[jtakw]

You should be able to adjust the formula with your Sheet Names and Cell Ranges/References for it to work.