Selecting non-blanks cells only

[nir020]

I have created a formula in excel that generates a range of cells. Within
that range there are some blank cells.

I wish to create a formula that looks in this range of cells and selects
only the cells with values in them, ignoring the blanks. It returns these
values in a new range with no blanks.

Can someone help?

Thanks

Nick

 

[Bernie Deitrick]

Nick,

Array enter (enter using Ctrl-Shift-Enter)

=INDEX($A$1:$A$10,SMALL(IF($A$1:$A$10<>"",ROW($A$1:$A$10)),ROWS($A$1:A1)))

Replace the three instances of $A$1:$A$10 with the address of your 'range of cells'

Then copy down until you get an error.

HTH,
Bernie
MS Excel MVP