Drop Down Box - Avoiding Blanks?

[Guest]

i am using excel 2003. i have a xls file with over 40 sheets. i have
created a list from the column starting on the cell#2 (below the header) and
ending to the last cell #65xxx, and named it facility. on another sheet i'm
using a drop down box validation to grab that list with the ignore blanks
checked but i am getting blank lines in my choices in the drop down, how can
i get rid of the blanks? i have created other lists thru out the sheets and
the drop down box will only populate with the data, there are no blank lines
to choose. i am creating the list the same way. i have also went to print
view and deleted all of the cells just to verify those cells are empty. why
are some drop downs have blanks and others don't?

thanks
cg

 

[Dave Peterson]

I think I'd use a dynamic range name.

You can read more about these dynamic range names at Debra Dalgleish's site:
http://www.contextures.com/xlNames01.html#Dynamic

 

[Guest]

i had tried using that formula from the link you posted prior of posting this
? and i kept getting an error in the formula. (i changed the sheet1 to the
appropriate sheet name)

i just don't understand why half of the drop downs work correctly and the
others has the data plus blank lines for choices.

does excel have a limit of # of drop downs or something?

thanks
cg

 

[Dave Peterson]

Do you have blanks just at the bottom of that list?

Then the naming technique at Debra's site should work.

If you have blanks throughout the list, I think (untested) that some of the
techniques at this page (also Debra's) might work:

http://www.contextures.com/xlDataVal03.html

 

[Guest]

I have found the reason why the drop down validation list box comes up with
blank lines. When building your ‘LIST’ range, you can not have any other
adjacent cells populated or you will get blank lines in your drop down box.

thanks
cg