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?

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" wrote in message
...
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?

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

Alain,

Yes that is it. Remember to array enter it.

--

HTH

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


"mcs51mc" wrote in
message ...

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

Bob Phillips Wrote:
Alain,

Yes that is it. Remember to array enter it.That array thing does it all :) Thanks a lot it works now


--
mcs51mc
------------------------------------------------------------------------
mcs51mc's Profile: http://www.excelforum.com/member.php...o&userid=28645
View this thread: http://www.excelforum.com/showthread...hreadid=482204