• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Drop down list Blank cells [SOLVED]

Good day PMMAN


Firstly welcome to the forum please read the green sticky's they will help you in the use of the forum.


As to your question:-


Do not put them there in the first place :)


No but seriously have you checked the range where you are pulling the data validation from to check for blank cells?


Good idea to do as srinidhi has suggested as well but no point in pulling blank cells in the first place
 
Hi ,


There are any number of links on this ; check this :


http://www.myonlinetraininghub.com/excel-ignore-blanks-in-data-validation-list


Narayan
 
For those who might find this thread in the future (as a result of links / searches elsewhere). (as I did :) )

The answer given by Srinhidi above is NOT correct.

The "ignore blanks" function on the data-validation setup in Excel does NOT mean "Ignore / do-not-show Blank cells within the data validation List" (although many of us who have used Excel for 20+ years wish that it did mean this !)

It means "If this cell has no entered value, then do not perform the defined validation." (So if the box is unchecked then attempting to leave the cell empty will result in an error/warning message as defined in your data validation setup).

Therefore, to ignore blank lines within a validation list you have to use the sort of complex ArrayFormulas that are explained on the link given by NARAYANK991 above.

(or as [B]bobhc[/B] very simply suggested above you try to design your data storage so that blank rows are not possible in your Spreadsheet where the data validation values are stored. Which means he is really saying nothing except the incredibly obvious for simple validation lists that you setup manually.

But when you want a validation list that is a SUB-SET of dynamically changed data in another part of your Workbook, dependent upon values in adacent columns that will be different at different times, then what bobhc is saying is uselessly simplistic because you will either need to replicate just the desired data into another contiguous place so that it can be used as a Validation List, or you will need a complex array formula that only returns the relevant values, as illustrated in various places online including the link given by NARAYANK991).




UNFORTUNATELY THERE IS NO SIMPLE WAY
 
Back
Top