Data Validation Error in Drop Down List

[snaplink22]

Hello,

I have a drop down list that includes four cells (B13 to B16) in cell A2. One of the cells that is being used in the list is blank so users can swap back to a blank cell if they want to change their selection. The challenge I'm running in to is that when a user changes their selection back to the blank cell I get a Data Validation error. This does not impact my sheet, except I keep getting questions from the users as to why the cell has the green shaded corner edge when they do that. Do you know of a way to correct this issue?

 

[sparky2205]

A couple of things:
• What error are your getting?
• What is the data validation rule? Does it preclude blanks?
• Why do you need the blank entry? Can the user not just press Delete to clear the entry from the cell?

 

[shaowu459]

Can you upload a piture to show the cell with the green shaded corner edge?

 

[snaplink22]

A couple of things:
• What error are your getting?
• What is the data validation rule? Does it preclude blanks?
• Why do you need the blank entry? Can the user not just press Delete to clear the entry from the cell?

Sadly, my users are older employees that are not very computer proficient so we've included a blank cell for them to select. The Data Validation List contains 8 cells, one of which is blank, and the Ignore Blank checkbox is not marked. Also, not sure if this is impacting it, but both cell A2 and cells B13 - B20 are in separate tables.

 

[snaplink22]

Can you upload a piture to show the cell with the green shaded corner edge?

I replied back to sparky with the some screenshots. Can you see those?

 

[shaowu459]

I replied back to sparky with the some screenshots. Can you see those?

There are several reasons may cause the green triangle, without checking the file i am not sure the exact reason. But, you can try following steps to see whether the green triangle will disappear. Select the cell, right click, format cells, in the last tab, select LOCK, and click OK to finish.

 

[snaplink22]

There are several reasons may cause the green triangle, without checking the file i am not sure the exact reason. But, you can try following steps to see whether the green triangle will disappear. Select the cell, right click, format cells, in the last tab, select LOCK, and click OK to finish.

All the cells are defaulted to Locked.

 

[shaowu459]

All the cells are defaulted to Locked.

Go to Excel Option----Formula Tab---error check rule or something like that, uncheck all the checkboxes

 

[snaplink22]

Go to Excel Option----Formula Tab---error check rule or something like that, uncheck all the checkboxes

Looks like removing Data entered in a table is invalid option in Formulas does the trick.

 

[shaowu459]

Yes, when the green triangle appears, it means the cell content triggered one of the checking rules.