Data Validation Not Working Correctly

Question

Hi everyone, I have a question.

I just installed Microsoft Office 2016, and when working in Excel 2016, I have just noticed that if I set up a Data Validation List that references a set of data to be in the drop down, Excel will let me generate this list, but if I save the program, and then close out, the Data Validation List I had just set up is not there anymore, completely deleted after I close out of the file.

Does anyone know why Excel would not be saving my Data Validation List when I close out of the file?

Answer 1

I first noticed this today.  I set up list-based validation and the validation is completely ignored.  Other validation works (e.g. Date > myStartDate, or entry is a positive whole number, etc.).

I SOLVED MY PROBLEM AS FOLLOWS:  

I wanted entries to be restricted to a list of saxophone mouthpieces.  My list field had blank rows at the end, to allow for adding other mouthpieces later.  I copied a nonsense string ("zzzzzzz") into each blank row, and then the validation worked - it would only allow entries from my mouthpiece list or blank entries.

--  Hope that helps.

Answer 2

Hi,

Let us uncheck the option “ignore blanks" checkbox. To do that open Excel—data---data validation---data validation---settings----choose a validation criteria and uncheck the option “ignore blanks" checkbox

Hope this information helps you. Please let us know if you need additional information.

Thank you.

Answer 3

Hi,

Let us uncheck the option “ignore blanks" checkbox. To do that open Excel—data---data validation---data validation---settings----choose a validation criteria and uncheck the option “ignore blanks" checkbox

Hope this information helps you. Please let us know if you need additional information.

Thank you.

Hi,

I have the same problem and "ignore blanks" didn't solve the problem.

Appreciate to receive the solution.

Answer 4

Hi Ali,

We would like to know more about your concern. Please answer the following:

• When did you first encounter this issue?
• What was your last activity when this issue happened?
• Any changes that took place in your computer before this issue occurred?