Drop down list Blank cells [SOLVED]
- Thread starter PMMAN
- Start date
bobhc
Excel Ninja
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
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
NARAYANK991
Excel Ninja
Hi ,
There are any number of links on this ; check this :
http://www.myonlinetraininghub.com/excel-ignore-blanks-in-data-validation-list
Narayan
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
)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
This answer is NOT Correct. It is technically inaccurate.
Please see my longer explanation further below in this thread.