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

[hotwhls55]

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!

 

[T. Valko]

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?

 

[Smitty]

Welcome to the Board!

I can't duplicate it in '10.

 

[hotwhls55]

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]

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]

Correct. Anything other than "Yes" or "No" generates an error. I couln't get it to hiccup on any of your examples.

 

[T. Valko]

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]

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]

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

 

[hotwhls55]

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.