Data Validation should not accept blank input

gord9b

Board Regular
Joined
Jun 12, 2002
Messages
247
I have a cell with a Data Validation with a list having the Source as a named range on the worksheet. However, if I blank out the cell, it is accepted. I tried selecting the 'IGNORE BLANK' button on the Data Validation form and unselecting it. Nothing works. How can I get this cell to reject a blank input and only allow the listed items?
Second, This source list is selected with an '=INDIRECT(A17)' statement. If A17 is changed, how can I blank out this cell(D17), thus forcing the person to select a valid input from the new list, considering the fact, the list will not accept a blank input if I can get an answer to my first question.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Chas17

Well-known Member
Joined
Oct 16, 2002
Messages
657
Place "<>0" as one of your cells in the named range.
i.e. the cells in range are a1,a2,a3
a1 = <>0
a2 = 1
a3 = 2

It will then not accept zero.
Please let me know if this is what you meant.
Thanks
 

gord9b

Board Regular
Joined
Jun 12, 2002
Messages
247
Sorry but this is not what I want. When the cell (D17) is selected and I type any invalid input, an error message prompts me to correct it. If I just blank out the cell, I can continue without any error message. I want the cell to not accept blanking out of the input without an error message popping up.
 

Forum statistics

Threads
1,143,640
Messages
5,719,987
Members
422,257
Latest member
Calion

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