Hi There,
I have a list of names in cells A1 to A10 (on sheet 1), I have put a data validation list in cell B1(on sheet2) and the selected area is A1 to A20, because names are going to be added to the list going forward.
How do i get rid of the blank cells in the drop down box? I have selected the "ignore blank cells" check box, but the blank cells still appear.
Please can someone explain to me how to do this? Thanks
I presume you used a named range for Sheet1!A1:A20 and are using that name in your data validation on Sheet 2.
You need to change the Refers To: to a dynamic range formula that expands and contracts for you. Change it to:
=$A$1:INDEX($A:$A,COUNTA($A:$A),1)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks