Data Validation Error in Drop Down List

snaplink22

Board Regular
Joined
Jul 6, 2018
Messages
129
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?
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

sparky2205

Active Member
Joined
Feb 6, 2013
Messages
294
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Apr 26, 2018
Messages
515
Office Version
  1. 365
Platform
  1. Windows
Can you upload a piture to show the cell with the green shaded corner edge?
 

snaplink22

Board Regular
Joined
Jul 6, 2018
Messages
129
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.

1589891274067.png
1589891322388.png
 

snaplink22

Board Regular
Joined
Jul 6, 2018
Messages
129

ADVERTISEMENT

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

Well-known Member
Joined
Apr 26, 2018
Messages
515
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 6, 2018
Messages
129

ADVERTISEMENT

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

Well-known Member
Joined
Apr 26, 2018
Messages
515
Office Version
  1. 365
Platform
  1. Windows
Yes, when the green triangle appears, it means the cell content triggered one of the checking rules.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,381
Messages
5,601,302
Members
414,440
Latest member
Kim0204

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top