Data Valdation Doesn't work when question marks (?) are entered

H

hotwhls55

New Member
Joined
Jul 15, 2011
Messages
8
Hello All,

I'm experience an issue with cells in Excel 2010 which I have Data Validation turned on for, the user's are to enter "Yes" or "No" and receive an error message if they don't to try again. I was having a pilot user run through some QA Testing on the document, and they found if they enter ?? or ??? they don't receive an error message instructing them to enter Yes or No.

I've since tested the following combinations: N?, ?N, Y??, Ye?, ?es, ??s none of which through the data validation error.

If they enter just one ? or four+ ?'s... the get the error message instructing them to enter Yes or No.

My analyst is "?" stands in place of any letter to which you're validating as a being successfully validating and fitting the validation criteria.

Do you know if there's a way to turn this off or write a macro to disable it from happening.

The reason I ask, is I have a lot of other logic and VBA going in the background based on rather or the user enters Yes or No... if any of the combinations where question marks are entered, my other logic picks up the user's input as if they enter "no"... so the entire process (checks and balances) begins to deteriorate.

Thank you!
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
hotwhls55 said:
Hello All,

I'm experience an issue with cells in Excel 2010 which I have Data Validation turned on for, the user's are to enter "Yes" or "No" and receive an error message if they don't to try again. I was having a pilot user run through some QA Testing on the document, and they found if they enter ?? or ??? they don't receive an error message instructing them to enter Yes or No.

I've since tested the following combinations: N?, ?N, Y??, Ye?, ?es, ??s none of which through the data validation error.

If they enter just one ? or four+ ?'s... the get the error message instructing them to enter Yes or No.

My analyst is "?" stands in place of any letter to which you're validating as a being successfully validating and fitting the validation criteria.

Do you know if there's a way to turn this off or write a macro to disable it from happening.

The reason I ask, is I have a lot of other logic and VBA going in the background based on rather or the user enters Yes or No... if any of the combinations where question marks are entered, my other logic picks up the user's input as if they enter "no"... so the entire process (checks and balances) begins to deteriorate.

Thank you!
Click to expand...
I don't have Excel 2010 to test this in but it works OK for me in Excel 2002.

How did you set the validation? What rule is applied?
 
Upvote 0
T. Valko said:
I don't have Excel 2010 to test this in but it works OK for me in Excel 2002.

How did you set the validation? What rule is applied?
Click to expand...

There's no rule, I simply am validating the data to match two cells, one containing Yes and the other containing No.

Settings tab
Allow: List ('Ignore blank' and 'In-cell dropdown' are both unchecked)
Data: Grayed out
Source: ='Drop Down'$A$3:$A$4

Input Message tab
Show input message when cell is selected = unchecked

Error Alert tab
Show error alert after invalid data is entered = checked
Style: Stop
Title: Yes/No Error
Error message: "Please enter "Yes" or "No" Thank you, PLIT Release Coordination
 
Upvote 0
Smitty said:
Welcome to the Board!

I can't duplicate it in '10.
Click to expand...
Thank you Smitty for the welcome message, I appreciate it.
Are you saying when you have set data validation on a sell to be Yes or No, referencing cells that contain those values, when you enter either 2 or 3 question marks, it errors for you and doesn't allow it as an entry?

Thank you,
Tim
 
Upvote 0
Correct. Anything other than "Yes" or "No" generates an error. I couln't get it to hiccup on any of your examples.
 
Upvote 0
hotwhls55 said:
There's no rule, I simply am validating the data to match two cells, one containing Yes and the other containing No.

Settings tab
Allow: List ('Ignore blank' and 'In-cell dropdown' are both unchecked)
Data: Grayed out
Source: ='Drop Down'$A$3:$A$4

Input Message tab
Show input message when cell is selected = unchecked

Error Alert tab
Show error alert after invalid data is entered = checked
Style: Stop
Title: Yes/No Error
Error message: "Please enter "Yes" or "No" Thank you, PLIT Release Coordination
Click to expand...
Ok, with those settings applied I can confirm this behavior in both Excel 2002 and Excel 2007.

This seems like a bug to me. If it happens in Excel 2002 it's hard to imagine that it's been around since then and has not been reported.

The work-around is easy enough.

Data Validation
Allow: Custom
Formula: =OR(cell_ref='Drop Down'$A$3:$A$4)

Replace cell_ref with the actual cell reference of the cell you're validating.
 
Upvote 0
T. Valko said:
Ok, with those settings applied I can confirm this behavior in both Excel 2002 and Excel 2007.

This seems like a bug to me. If it happens in Excel 2002 it's hard to imagine that it's been around since then and has not been reported.

The work-around is easy enough.

Data Validation
Allow: Custom
Formula: =OR(cell_ref='Drop Down'$A$3:$A$4)

Replace cell_ref with the actual cell reference of the cell you're validating.
Click to expand...
Thank you Biff!

I will give it a shot, what do I have to use... the cell_ref though is a range of 236 cells in a column - I'm certain I'll be able to make it work.

Thanks again - I'll post back once I get it implemented and have validated the fix works in Excel 2010.

Tim
 
Upvote 0
hotwhls55 said:
Thank you Biff!

I will give it a shot, what do I have to use... the cell_ref though is a range of 236 cells in a column - I'm certain I'll be able to make it work.

Thanks again - I'll post back once I get it implemented and have validated the fix works in Excel 2010.

Tim
Click to expand...
If the range of cells to be validated is a contiguous block then you can apply the validation all at once with the formula referencing a relative cell address.

For example, if you want to validate the range A1:A10 you'd select the entire range A1:A10 starting from cell A1. Then the formula would be relative to cell A1:

Data Validation
Allow: Custom
Formula: =OR(A1='Drop Down'$A$3:$A$4)

One thing to note is that in earlier versions of Excel you can't directly refer to another sheet in validation (and other areas). Being able to directly refer to other sheets is a new feature of Excel 2010.

I need to get Excel 2010 (but first I need to get a new machine! :mad:).
 
Upvote 0
Thanks again Biff! I hope to have it in place (and working) by this afternoon PDT, I will be sure to report back... hopefully with confirmation it works.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
How to fix VBA code to stop saving and emailing PDF of blank cell from data validation list?
Replies
2
Views
298
lharr28
L
C
  • Solved
Abort macro if no data
Replies
3
Views
447
CordingBags
C
L
  • Locked
  • Question Question
How to stop VBA from creating pdf from drop-down list if the value is empty?
Replies
1
Views
340
Fluff
Fluff
V
  • Question Question
Unprotect doesn't work with VBA code
Replies
2
Views
590
VIorica000
V
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
426
STARL0RD
S

Forum statistics

Threads
1,224,811
Messages
6,181,081
Members
453,021
Latest member
Justyna P

Share this page

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