Highlighting only 1 cell

[collettegee]

Hi I have written the following formula as a conditional formula =AND(C$20=Sheet1!$D$46,C21>=Sheet1!$D$47)
which highlights values based on 2 criteria. This can give me up to 3 results and i only want the closest one to one of the specific criteria values not all possible answers. How do i modify the formula to do this please. .

 

[vladimiratanasiu]

Hi I have written the following formula as a conditional formula =AND(C$20=Sheet1!$D$46,C21>=Sheet1!$D$47)
which highlights values based on 2 criteria. This can give me up to 3 results and i only want the closest one to one of the specific criteria values not all possible answers. How do i modify the formula to do this please. .

View attachment 71818

Add to your message not a confusing image, but clear information, as a table with visible references, values, formulas etc., Use add-on XL2BB - Excel Range to BBCode, Dropbox etc.

 

[collettegee]

I hope these images are correct



=AND(C$12=Sheet1!$D$26,C13>=Sheet1!$D$27) is the conditional formatting

 

[vladimiratanasiu]

I hope these images are correct



=AND(C$12=Sheet1!$D$26,C13>=Sheet1!$D$27) is the conditional formatting

View attachment 71842

View attachment 71843

Is it what you need?

Book1
	C	D	E	F	G	H
11
12	3	4	5	6	7	8
13	2.20	1.80	1.30	0.70	0.00	0.00
14	0.00	3.00	2.80	2.40	1.90	1.50
15	0.00	3.70	3.30	3.00	2.70	2.40
16
17
18
19
20
21
22
23
24
25
26		5
27		0.5
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E13:E15	Expression	=COUNTIF(D$13:D13,">"&D$27)=1	text	NO

 

[collettegee]

Thanks for this would you put it alongside the other expression?

 

[vladimiratanasiu]

Book1
	C	D	E	F	G	H
12	3	4	5	6	7	8
13	2.20	1.80	1.30	0.70	0.00	0.00
14	0.00	3.00	2.80	2.40	1.90	1.50
15	0.00	3.70	3.30	3.00	2.70	2.40
16
17
18
19
20
21
22
23
24
25
26		5
27		0.5
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D13:D25	Expression	=COUNTIF(D$13:D13,">"&D$27)=1	text	NO

 

[vladimiratanasiu]

Thanks for this would you put it alongside the other expression?

I don't understand. The other function didn't work, according to your previous need. What's the goal of the new request?