Drop-down list and error message

Schoueps

New Member
Joined
Jun 17, 2014
Messages
2
Hi all,
I have a question concerning drop-down list and error message. I have two lists (range name: Status1 and Status2), the lists to be shown in cell A2 depends on wether cell B2 is empty or not (in B2 user can write what he wants, no drop-down list).
To create the drop-down list in cell A2 I wrote the following: =IF($B9="",Status1,Status2).
It works fine. The problem I encounter is the error message not showing up if the user enters in A2 a value that doesn't correspond to list items (although the box for error is checked). Actually when B2 contains a value and then I enter a value in A2 that is wrong, the error message appears, if B2 is empty, any value can be entered (no error message) althoug only items from Status1 should be accepted.
I don't understand what's going on.
Thanks a lot for your input and help ! :)
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
After many experiments, the following seems to be a solution:
The dialog to create the data validation list contains checkbox "Ignore blanks". If you un-check it, then the data validation starts behaving correctly.

J.Ty.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,875
Members
452,363
Latest member
merico17

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
Back
Top