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

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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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!
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?
 

hotwhls55

New Member
Joined
Jul 15, 2011
Messages
8
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?

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
 

hotwhls55

New Member
Joined
Jul 15, 2011
Messages
8

ADVERTISEMENT

Welcome to the Board!

I can't duplicate it in '10.
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
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Correct. Anything other than "Yes" or "No" generates an error. I couln't get it to hiccup on any of your examples.
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623

ADVERTISEMENT

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
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.
 

hotwhls55

New Member
Joined
Jul 15, 2011
Messages
8
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.
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
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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
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:).
 

hotwhls55

New Member
Joined
Jul 15, 2011
Messages
8
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,915
Messages
5,638,970
Members
417,062
Latest member
Canucks21

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