Conditional formating question

[Polanskiman]

I have this rather complex file but I simplified my issue by making a mock up on a new file to ask the question here. So here is the deal.

Sheet 1. I have this list of products in that specific order. When a product is selected with 'x' then the amount shows up.

Book1.xlsx
	C	D	E
4	Product	Selected	Amount
5	product 4	x	1
6	product 2		0
7	product 3	x	1
8	product 16		0
9	product 5		0
10	product 9		0
11	product 12	x	1
12	product 11		0
13	product 1		0
14	product 13		0
15	product 10	x	1
16	product 15		0
17	product 14	x	1
Sheet1

Cell Formulas
Range	Formula
E5:E17	E5	=IF(D5="x",1,0)

Sheet 2. This is the cost sheet. Products are in that specific order and some products are not in Sheet 1 (for instance product 6, 7 and 8). You can see both sheets have different product orders. That's the way it is and cannot be changed.

Book1.xlsx
	C	D	E	F
2	Product	Amount	Unit price	Cost
3	product 1	0	5	0
4	product 2	0	2	0
5	product 3	1	6	6
6	product 4	1	5	5
7	product 5	0	1	0
8	product 6	-	-	-
9	product 7	-	-	-
10	product 8	-	-	-
11	product 9	0	2	0
12	product 10	1	6	6
13	product 11	0	8	0
14	product 12	1	9	9
15	product 13	0	1	0
16	product 14	1	4	4
17	product 15	0	2	0
18	product 16	0	6	0
Sheet2

Cell Formulas
Range	Formula
D3	D3	=Sheet1!E13
D4:D5,D7	D4	=Sheet1!E6
D6,D11,D13,D15,D17	D6	=Sheet1!E5
F3:F7,F11:F18	F3	=D3*E3
D12	D12	=Sheet1!E15
D14	D14	=Sheet1!E11
D16	D16	=Sheet1!E17
D18	D18	=Sheet1!E8

What I need is for products in Sheet 2 to be highlighted when there is an amount for that product. I have manually highlighted the cells for your understanding. Now, I know this can very easily be done by doing 1 conditional formating for each and every product/line but I would rather have only 1 conditional formating rule for the whole list that could do the job. The idea is to have the least amount of Conditional formating rules are possible.

How can this be achieved and if possible without the need of a helper column?

Thank you.

 

[Peter_SSs]

What about this?

Polanskiman.xlsm
	C	D	E	F
2	Product	Amount	Unit price	Cost
3	product 1	0	5	0
4	product 2	0	2	0
5	product 3	1	6	6
6	product 4	1	5	5
7	product 5	0	1	0
8	product 6	-	-	-
9	product 7	-	-	-
10	product 8	-	-	-
11	product 9	0	2	0
12	product 10	1	6	6
13	product 11	0	8	0
14	product 12	1	9	9
15	product 13	0	1	0
16	product 14	1	4	4
17	product 15	0	2	0
18	product 16	0	6	0
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D3:F18	Expression	=AND($D3<>0,$D3<>"-")	text	NO

 

[Polanskiman]

Oh boy... That was pathetically easy ?. Shame on me.
Thank you Peter. You're the best!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

No shame - it is what the forum is for.

BTW, here is an even simpler way. ?

Polanskiman.xlsm
	C	D	E	F
2	Product	Amount	Unit price	Cost
3	product 1	0	5	0
4	product 2	0	2	0
5	product 3	1	6	6
6	product 4	1	5	5
7	product 5	0	1	0
8	product 6	-	-	-
9	product 7	-	-	-
10	product 8	-	-	-
11	product 9	0	2	0
12	product 10	1	6	6
13	product 11	0	8	0
14	product 12	1	9	9
15	product 13	0	1	0
16	product 14	1	4	4
17	product 15	0	2	0
18	product 16	0	6	0
Sheet2 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D3:F18	Expression	=-$D3	text	NO

 

[Polanskiman]

That's an interesting one. Can you explain why adding a minus to the reference makes this work?

 

[Peter_SSs]

Can you explain why adding a minus to the reference makes this work?

CF formats the cell if the condition is True and does not if the condition is False, or an Error occurs. Excel treats the number zero as a False and any other number (positive or negative) as True.
So,
- for the zero cells -0 =0 so that is a False -> no format applied
- for any other numerical value (yours are all 1) the minus in front just 'swaps' the number from plus to minus or vice-versa but it will still be a non-zero value which counts as True -> format is applied
- for the "-" cells (or any other text) putting a minus in front results in an error since the numerical 'swap' cannot be done -> no format applied

 

[Polanskiman]

Beautiful! Thank you.

 

[Peter_SSs]

Cheers.