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!

 

[hotwhls55]

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

I've just tried... either it's because it is Friday afternoon and my brain isn't working correctly... or the above format isn't support in Excel 2010.

I'm trying to validate cells in the range D6:D236 to equal 'Drop Down'$A$3:$A:$4.

I've attempted variations on your supplied format (thanks again for trying) and for the life of me it's not working. Perhaps it's one of those times where I just need to let it die today, come back tomorrow (Monday actually) and I'll get it working on the first try.

 

[T. Valko]

I've just tried... either it's because it is Friday afternoon and my brain isn't working correctly... or the above format isn't support in Excel 2010.

I'm trying to validate cells in the range D6:D236 to equal 'Drop Down'$A$3:$A:$4.

I've attempted variations on your supplied format (thanks again for trying) and for the life of me it's not working. Perhaps it's one of those times where I just need to let it die today, come back tomorrow (Monday actually) and I'll get it working on the first try.

Are you getting some kind of error?

What exactly does "not working" mean?

 

[spvarapu]

Hi Biff,

I want to show Dropdown as well with this formula to not allow ?? s...Thanks in advance.

 

[T. Valko]

Hi Biff,

I want to show Dropdown as well with this formula to not allow ?? s...Thanks in advance.

Need more information.

 

[Simon Bone]

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.

I find it surprising but I'm not sure if it will be seen as a bug.

It looks like the comparison is done with LIKE to me. I can get ? to match any single letter or * to match any number of letters.

I can't see where this behaviour is documented but I wouldn't be surprised if I've just missed it.

 

[T. Valko]

I find it surprising but I'm not sure if it will be seen as a bug.

They are reluctant to classify anything as a bug since it detracts from their reputation.

Who wants to buy bug ladened software?