Highlight lowest 3 values in an array

[noahsmom]

Tried the following & several other ways:

Using conditional formatting - Cell Value is Equal To...

Condition 1
=MIN(H7,P7,X7,AF7,AN7,AV7,BD7,BL7,BT7,CB7,CJ7,CR7,CZ7,DH7,DP7,DX7,EF7,EN7)
Highlight yellow
Condition 2
=SMALL((H7,P7,X7,AF7,AN7,AV7,BD7,BL7,BT7,CB7,CJ7,CR7,CZ7,DH7,DP7,DX7,EF7,EN7),2)
Highlight green
Condition 3
=SMALL((H7,P7,X7,AF7,AN7,AV7,BD7,BL7,BT7,CB7,CJ7,CR7,CZ7,DH7,DP7,DX7,EF7,EN7),3)
Highlight blue

gives me the following error:
You may not use unions, intersection, or array constants for conditional formatting criteria.


Based upon a spreadsheet that looks like this...

Sample.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	(Identifying					Vendor	123
2	Information)					Contact	Jane
3						Quote #	1A2B
4						Valid	12/31
5
6	Item#	ItemDesc	Qty	UOM	UnitCost	ExtCost	Bid	ExtBid	Terms	Delivery	Freight	FOB	StdPkg	MinBuy
7	226801	ABC	2	ea	1.55	3.10	1.60	3.20		7	Add		5	5
8	226802	DEF	11	ea	1.51	16.61	1.57	17.27		7	Add		5	5
9	226803	HIJ	2	ea	1.56	3.12	1.61	3.22		7	Add		5	5
10	226807	KLM	4	ea	2.36	9.44	2.40	9.60		7	Add		5	5
11	226808	NOP	6	ea	0.28	1.68	0.32	1.92		7	Add		5	5
12	226809	QRS	2	ea	1.69	3.38	1.73	3.46		7	Add		5	5
13	226810	TUV	1	ea	2.04	2.04
14	244500	WXYZ	5	ea	10.23	51.15	10.27	51.35		7	Add		5	5
Sample

columns G thru N may be repeated numerous times (different vendors). I need the lowest 3 values under the EXT COST column excluding the blank cells. The array will always remain the same (H7,P7,X7,AF7,AN7,AV7,BD7,BL7,BT7,CB7,CJ7,CR7,CZ7,DH7,DP7,DX7,EF,EN).

Since conditional formatting doesn't seem to be doing the trick, does anyone have any additional ideas? Possibly a macro?

I hope that I have provided enough information. ANY help would be more than greatly appreciated!!!!

Thanks,
Noahsmom ray:

 

[GlennUK]

An example

Try this for condition 2 ( and adjust for condition 3 ), instead of Cell Value Is:

=SMALL((H7,P7,X7,AF7,AN7,AV7,BD7,BL7,BT7,CB7,CJ7,CR7,CZ7,DH7,DP7,DX7,EF7,EN7),2)

do Formula Is, with this formula:

=H7=SMALL(IF(MOD(COLUMN($H$7:$EN$7),8)=0,$H$7:$EN$7,9999),2)

 

[noahsmom]

Thanks a million for the example, but I can't seem to make it work. It's highlighting the blank cells yellow and then several others green, and nothing blue.

I even made several futile attempts in modifying the code. Another suggestion maybe?

 

[GlennUK]

Select only cells H7,P7,X7,AF7,AN7,AV7,BD7,BL7,BT7,CB7,CJ7,CR7,CZ7,DH7,DP7,DX7,EF7,EN7 ( click H7, hold the ctrl key, then click each of the other cells ), before doing the Conditional Formatting.

The example I gave was for Condition 2. You can use =B7=MIN(etc for Condition 1. Use my formula with a ,3 instead of ,2 for Condition 3.

 

[noahsmom]

Thanks again, Glenn. But....I selected the referenced cells using the contol key, chose conditional formatting with:

Condition 1
Cell Value is Equal to
=MIN(H7,P7,X7,AF7,AN7,AV7,BD7,BL7,BT7,CB7,CJ7,CR7,CZ7,DH7,DP7,DX7,EF7,EN7)
Condition 2
Formula is
=H7=SMALL(IF(MOD(COLUMN($H$7:$EN$7),8)=0,$H$7:$EN$7,9999),2)
Condition 3
Formula is
=H7=SMALL(IF(MOD(COLUMN($H$7:$EN$7),8)=0,$H$7:$EN$7,9999),3)

and I'm at a loss. It still doesn't work for me. It should, I KNOW it should, but it ISN'T. I'm so frustrated.

 

[GlennUK]

Condition 1 should have been:

=H7=MIN(H7,P7,X7,AF7,AN7,AV7,BD7,BL7,BT7,CB7,CJ7,CR7,CZ7,DH7,DP7,DX7,EF7,EN7)

Can you fix that one, and see if it all starts working?

 

[noahsmom]

Changing Condition 1 to =H7=MIN... highlights the BLANK cells yellow.

Condition 1 will work correctly by itself without the =H7 and with adding your Condition 2, but then the Condition 2 doesn't work correctly. And then Condition 3 isn't working either.

I'm beginning to feel like a complete moron. I know that it is something so extremely simple...

Again, I sincerely appreciate your trying to help with this.

 

[GlennUK]

How can it be highlighting the blank cells? You should have selected the cells H7,P7,X7,AF7,AN7,AV7,BD7,BL7,BT7,CB7,CJ7,CR7,CZ7,DH7,DP7,DX7,EF7,EN7 for Conditional Formatting ... which doesn't include the blank cells ( or does it???? ).

Anyway, do any of your cells get highlighted Green?

 

[noahsmom]

Yes, some of these cells (H7,P7,X7,AF7,AN7,AV7,BD7,BL7,BT7,CB7,CJ7,CR7,CZ7,DH7,DP7,DX7,EF7,EN7) CAN be blank.

And Yes, several of the selected cells get highlighted green (Cond 2) but none get highlighted blue (Cond 3).

 

[GlennUK]

Ah, it's the blank cells that will be causing the problems. I have to go now. I'll look at this tomorrow and try to design some condition formulae then.