Validation List Box:-blank cells in my list range

[SMBR]

I am using a validation list box but would like the list to not show any
blank cells that are in my list range. Is that possible?

[Bob Phillips]

You would need to filter out the blanks into a new list and show that.

This formula will create a new such list

=IF(ISERROR(SMALL(IF($A$1:$A$20<>"",ROW($A1:$A20),""),ROW($A1:$A20))),"",
INDEX($A$1:$A$20,SMALL(IF($A$1:$A$20<>"",ROW($A1:$A20),""),ROW($A1:$A20))))

it is an array formula, select a range of cells for the no-blanks data, ad
the formula to the formula bar, and Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SMBR" <SMBR@discussions.microsoft.com> wrote in message
news:6F09FF94-35EA-457E-A739-B3E0C6C5D0C8@microsoft.com...
> I am using a validation list box but would like the list to not show any
> blank cells that are in my list range. Is that possible?

[mcs51mc]

Hi Bob,

I tried out your formula but it didn't work well

Is this what your formula should do?
1) list with blanks in cells A1:A20
2) copy 20 times your formula in, let say, C1:C20 so that this range contains no blanks (except at the end af course)

Thanks for any reaction
Alain

[Bob Phillips]

Alain,

Yes that is it. Remember to array enter it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"mcs51mc" <mcs51mc.1y6i1a_1131454203.2952@excelforum-nospam.com> wrote in
message news:mcs51mc.1y6i1a_1131454203.2952@excelforum-nospam.com...
>
> Hi Bob,
>
> I tried out your formula but it didn't work well
>
> Is this what your formula should do?
> 1) list with blanks in cells A1:A20
> 2) copy 20 times your formula in, let say, C1:C20 so that this range
> contains no blanks (except at the end af course)
>
> Thanks for any reaction
> Alain
>
>
> --
> mcs51mc
> ------------------------------------------------------------------------
> mcs51mc's Profile:
http://www.excelforum.com/member.php...o&userid=28645
> View this thread: http://www.excelforum.com/showthread...hreadid=482204
>

[mcs51mc]

Alain,

Yes that is it. Remember to array enter it.

That array thing does it all Thanks a lot it works now