remove blanks from data validation list

**jame24** · 04-27-2012, 03:44 PM

Hey Everyone,

I was wondering if you would be able to help me be able to remove blanks from a data validation lst.

the list is one enough to be able to enter additional names based on how many names the user enters so the list length is alwas changing.

is their anyway to remove blancks from the list?

Cheers

**jeffreybrown** · 04-27-2012, 03:54 PM

You need to use a dynamic named range

MyRange =Sheet2!$A$1:INDEX(Sheet2!$A:$A,MATCH(REPT("z",255),Sheet2!$A:$A))

In this case, my list is in Sheet2 starting in A1. This will look down to the last name and stop.

In the data validation >> List >> MyRange

**Ron Coderre** · 04-27-2012, 03:55 PM

I believe a Dynamic Range Name is what you need. It automatically expands/contracts to accommodate the data.

Insert.Name.Define
Names in Workbook: LU_MyList
Refers to: =OFFSET(Sheet1!$B$1,0,0,COUNTIF(Sheet1!$B:$B,"?*"),1)
(Expands/Contracts to include only non-blank ("") cells.

See this site for detailed instructions:
http://www.contextures.com/xlNames01.html#Dynamic

Does that help?

**jame24** · 04-27-2012, 04:38 PM

HEY THANKS FOR the info...it worked perfectly except for some reason i am still getting a blank cell as an option?

any help would be greatly appreciated thanks