I Dont want Blank cell in Drop Down cell.

**tariqnaz2005** · 03-09-2010, 07:27 AM

Hello all,

Hope you all will be fine..I m back again cuz some thing stuck me and needs your help to solve it..

As per attached example file..i have a dropdown cell in sheet 2 cell D2, which gets a name from a list as i fix in validation"Name".

I have problem when i click drop down, i list appears but it consist of blank cells also and not uniform in order from a to z..

What i want is if i click D2, a list should come duly uniromed and without blank cells or rows.. Can it possible? cuz my mind is not working here..

Thanks in advance for any help.

**teylyn** · 03-09-2010, 07:30 AM

Hi tariqnaz,

the data validation list will show the range exactly as it is. If your range contains blanks, then the data validation list will contain blanks.

If you don't want to see blanks in your data validation list, then you'll have to create a named range without blanks.

**contaminated** · 03-09-2010, 07:38 AM

Hi
Take a look at this workook. Is this what you want?

**tariqnaz2005** · 03-09-2010, 09:56 AM

any help pls?

**NBVC** · 03-09-2010, 10:01 AM

Please respond to the posters that have kindly responded to you already!

**deemo** · 03-09-2010, 10:01 AM

Hi tariqnaz2005 - as teylin said, the range you define for the dropdown must not contain blanks. Had a look at your workbook but it seems to work ok... what exactly is not working?

**ChemistB** · 03-09-2010, 01:44 PM

The solution Contaminated supplied seems to be working. Check out Sheet1 Columns K and L.

**tariqnaz2005** · 03-10-2010, 06:54 AM

Thanks Contaminated...

Your idea is totally working ..its great..thanks..but can i have this drop down box in asending order (uniform)?

I want another thing might be you can do it..what i want if instead of drop down in cell d2 of sheet2 of your attachment, i want a search type thing..so that if i type there ap part of name and it return me full name of all maching name in sheet1 column C..so that i can select name and put there..it will same my time..cuz in dorop down it is not coming in order and take time to search..cuz my list has more than 1000.

can you do it..

thanks..

**teylyn** · 03-10-2010, 07:00 AM

tariqnaz, I realize that English is probably not your first language, but could you please put some more care into constructing your posts? Please refrain from chat room jargon and use some punctuation to separate sentences. This will make it easier for people here to understand your posts.

Please understand that for a lot of other members here English is also not their first language, so please try to make your posts as understandable as you can.

Also, please include your country in your profile. This will make it easier for people to overcome the language barrier.

thanks

**tariqnaz2005** · 03-10-2010, 08:19 AM

hi, Contaminated ,

Thanks for help and great idea..its work properly..wow..wounderfull..but still i want this drop down in order like

A
B
C
D

asending ..

Can it possible?

**DonkeyOte** · 03-10-2010, 09:25 AM

Using contaminated's prior file change as follows:

Sheet1

K2: =IF($C2="","",1+COUNTIF($C$2:$C$29,"<"&$C2))
copied down to K29

L2: =INDEX($C$2:$C$29,MATCH(ROWS(L$2:L2),$K$2:$K$29,0))
copied down to L29

Define the RefersTo of your Named Range with:

=Sheet1!$L$2:INDEX(Sheet1!$L:$L,MATCH("*",Sheet1!$L:$L,-1))

(in all of the above modify delimiter as nec. - ie from comma to semi colon pending your locale)

**tariqnaz2005** · 03-10-2010, 10:07 AM

Wounderfull...Thanks...I never believe it that this could happend..

Thanks..Donkeyote..you always performed nice...thanks all for help..thanks contaminated,,

God Bless you..