Want a data validation droplist with empty cell as an option

**rcurious** · 06-11-2012, 10:54 AM

Hello,

I want a data validation droplist with empty cell as an option. I do not want to create a source list somewhere in the sheet, I want, that empty cell was described/programmed in data validation only.
Is that possible?

**icestationzbra** · 06-11-2012, 11:09 AM

i was probably unable to completely understand your question.

if you want to create a Data Validation list without a source list in a sheet, then just put the values in the Source box separated by commas, such as, "January, February, March" (without the quotes). if you want to allow a blank value to be selected, just do not select anything at all in the cell.

**rcurious** · 06-11-2012, 11:15 AM

Sorry, my english is not perfect, but you understood, what I wanted to say. I want to allow a blank value to be selected and without a source list in a sheet. The bad thing is that if I once select something from the list - there is no way to make that cell look blank again.

**SteveG** · 06-11-2012, 11:18 AM

Hi,

If you select "Ignore Blanks" you can simply delete your selection from the cell to return it to blank.

Steve

**sratcliff** · 06-11-2012, 11:21 AM

If you simply hit delete, the cell will be cleared, and your validated data will stil be there. You can also leave the top cell of your list empty.

**ChemistB** · 06-11-2012, 11:22 AM

If you "clear contents" or hit delete or if you backspace over the entry (therefore clearing it), there should not be an issue changing the value. In the Data Validation Window, make sure "Ignore Blank" is checked.

**icestationzbra** · 06-11-2012, 11:22 AM

Originally Posted by Romkeris

Sorry, my english is not perfect,

no sweat - same problem in US, english not perfect ;-)

**rcurious** · 06-11-2012, 11:26 AM

Originally Posted by sratcliff

If you simply hit delete, the cell will be cleared, and your validated data will stil be there. You can also leave the top cell of your list empty.

Thanks. Delating cell value, suites me. Forgot about that.
The way of leaving the first value blank in the data validation, does'nt work. It doesn't come up from the droplist, it works only if data validation comes from sourcelist somewhere in the sheet.

**icestationzbra** · 06-11-2012, 01:10 PM

that is right - leave alone the first, any blank value inserted in a intrinsic Data Validation Source is disregarded (blanks anywhere in the list). but, mind you, white spaces around a value are indeed regarded, i.e, 'a,b,' and 'a , b ,' are values with different lengths when they show up in the dropdown list.

**xorex** · 03-24-2017, 03:39 PM

This can seem confusing, at first.

The Data Validation's LIST option is meant to point to a list in the spreadsheet.
The manual entry of actual values in the LIST box is just a 2nd method.
So, create a list somewhere in your spreadsheet that lists all possible values.

In my case, I had a legend at the top of the sheet with the values I wanted to allow.
E.g., A1=Y, A2=N, A3=(blank), A4=?
Note that A3 is just a blank cell - not the text "(blank)"
I then selected that vertical list and set its range name to "Status_List"
Finally, in the validation option's LIST box, I entered =Status_List
Voila. The drop down shows Y, N, (blank), and ?

Even better, any time you want to change the list, just change the range of values.
Every Validation option using Status_List will instantly be updated.
So there is not need to manually edit one cell's validation and copy to all others.