Data validation problem (double drop down list)

please post your workbook WITHOUT any protection (desensitize any data) and explain how you are protecting the worksheet. Will try to protect and recreate the error.
To attach a workbook:
-click on reply
-then "Go Advanced"
-then look below for "Manage Attachments"

Thx for your interest in this matter!
It's a quite large workbook, but the part of the workbook, or one of the parts, where the problem occurs is attached here.
Now I noticed that the problem occures even though the sheet is not protected, but in this case I can still select the cell by clicking on it, and if the sheet is protected, I have to navigate to it with the arrow keys on the keyboard.

Any suggestions are very much appreciated.

I have not investigated why the problem occurred, but here is a fix:

On sheet "Crew List"
-click on View Tab and check the "Headings" box so that row numbers are visible
-right click on row10 and select Insert (= insert new row)
-enter same values as row9 into row10 (columns A,B,C,E,F)
-right click on row9 and select Delete
-click on View Tab and uncheck the "Headings" box so that row numbers are not visible

Thank you, I will try this approach...
Its quite interesting, because the same issu suddenly occurred on several of my "input sheets" in the workbook.

I have never seen this happen before. It could be a "bug" in Excel2016
Did it start to happen AFTER you upgraded to Excel2016?
You say that it happens on several input sheets - does it always occur only in first cell (in any column) with data validation?

It is possible that it happend after updating to Excel 2016. I'm using the workbook on a public workplace computer with Excel 2013, and after making "core changes" to the workbook on my personal computer (cant't remember if it happend after updating to Excel 2016) the problem occurred for the first time. Sadly I did't notice it directly, so I don't have a old version where this problem dosn't occurre. So I can't simulate the similar conditions to find out when it happend.

For most parts it's only on the firs cell in every column using data validation, however, I noticed one place where it happend on the first and last cell in a column.

In that case, I do not think we can analyse the cause further.
After testing the workaround can you mark the thread as solved?
thanks

This woraround works fine for solving the problem in hand... However, in my case, where the workbook is quite large and have a lot of references to the cell in question, there can be quite a lot of work to duoble check and correct all references to the cell in hand.

Originally Posted by Kevin#

I have not investigated why the problem occurred, but here is a fix:

On sheet "Crew List"
-click on View Tab and check the "Headings" box so that row numbers are visible
-right click on row10 and select Insert (= insert new row)
-enter same values as row9 into row10 (columns A,B,C,E,F)
-right click on row9 and select Delete
-click on View Tab and uncheck the "Headings" box so that row numbers are not visible

Here is a simple macro that you could add to the workbook to identify every cell that has data validation
Creates sheet called "DataValidation" and writes list (by sheet) of all cells with data validation

Please Login or Register  to view this content.

Thx, this will be very helpful. Thank you for your interesti in this matter Kevin# ....

After cleaning up my workbook I actually noticed when the problem occurs.
It seems to happen in Excel 2016;
when saving the workbook, all cells with "data validation->list" that is selected gets this problem when saving and cloasing the workbook. However, this dosen't happen in Excel 2013, so it seems to be a Excel 2016 problem.

So, it is possible to clean up a workbook using the method above, however there is a possibility tha the problem will reoccur in the workbook again.

Originally Posted by bilenberg

After cleaning up my workbook I actually noticed when the problem occurs.
It seems to happen in Excel 2016;
when saving the workbook, all cells with "data validation->list" that is selected gets this problem when saving and cloasing the workbook. However, this dosen't happen in Excel 2013, so it seems to be a Excel 2016 problem.

So, it is possible to clean up a workbook using the method above, however there is a possibility tha the problem will reoccur in the workbook again.

My solution;
If using Excel 2013, this problem dosen't excist.

For computers with Excel 2016, I use a workaround:
simpley added a macro that runs automatically every time the workbook is saved, the macro just selectes cells on every sheet where Data Validation->List is not in use.

Please Login or Register  to view this content.