VALIDATION LIST

[Guest]

Hello. Can anyone tell me how to create a validation list and not show blank
cells or duplicates? I thought this was the default behavior of validation
lists, but it doesn't seem to be the case. Im using Excel 2003 SP1. Thanks.

 

[Tom Ogilvy]

You just highlight a one column wide block of cells. It shows what is in
those cells.

In the box displayed by the list option

=Sheet1!$A$1:$A$10

If you don't want to show blank cells, then create a dummy range where you
place your data wiithout blank cells or create a defined name using formulas
that remove the blanks.

Perhaps you can get more insights at Debra Dalgleish's sight:
http://www.contextures.com/tiptech.html Look for Data Validation.

--
regards,
Tom Ogilvy

Hello. Can anyone tell me how to create a validation list and not show blank
cells or duplicates? I thought this was the default behavior of validation
lists, but it doesn't seem to be the case. Im using Excel 2003 SP1.

Thanks.

 

[Guest]

Create a Named Range that is a dynamic range rather than static. Then in
Data>Validation>List RefersTo enter "=MyRange" with equal sign but
not the quotes; and of course use the name of your dynamic range.
If needed, sort your dynamic range to place all blanks at the bottom.