Creating an array from multiple, sepearated cells

Hi
perhaps

Please Login or Register  to view this content.

replace the comma with ; according to your regional settings

You can create a named range, say, MyRange

Keep pressed the Ctrl key and select H2, L2 and P2
in the Name box, type
MyRange
hit Enter

Select H2 and use the formula option in CF
1st rule
=H2=SMALL(MyRange,1)
pick the format you want

2nd rule
=H2=SMAL(MyRange,2)
pick the format you want

use the Format Painter (brush) to copy the CF to L2 and P2

Hope this helps

The SMALL function will accept multiple area references.

Try it like this:

=D2<=SMALL(($D2,$H2,$L2,$P2),2)

EDIT: However, that syntax is still not allowed in refedits (those little boxes in userforms where you enter a formula or a range reference).

This syntax is allowed:

=D2<=SMALL(CHOOSE(ROW(INDIRECT("1:4")),D2,H2,L2,P2),2)

If you test that formula in a worksheet cell it must be array entered.

Array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

If you want different formats for SMALL 1 and SMALL 2 then use these formulas:

For SMALL 1:

=D2=MIN(D2,H2,L2,P2)


For SMALL 2:

=D2=SMALL(CHOOSE(ROW(INDIRECT("1:4")),D2,H2,L2,P2),2)

Did you try this one:

For SMALL 2:

=D2=SMALL(CHOOSE(ROW(INDIRECT("1:4")),D2,H2,L2,P2),2)

It should work if I understand what you want to do.

If you enter that formula in a worksheet cell it must be array entered.

Array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Do you want the smallest value to be one format and the 2nd smallest value to be another format?

What if these are the values:

D2 = 10
H2 = 10
L2 = 20
P2 = 20

Which cell is the smallest and which is the 2nd smallest?

Which cells should be formatted?

Yes, just to clarify: formulas entered in Conditional Formatting are automatically array-entered, so no need to manually enter the curly brackets.

Of course, as Tony points out, when entered in the worksheet, they still require the usual CTRL+SHIFT+ENTER.

Regards

Good deal. Thanks for the feedback!