Wild card issue with validation list

[Audiology]

I have a data validation formula as follows:

=OR(ISNUMBER(MATCH(H75, $AU$3:$AU$40, 0)), ISNUMBER(MATCH($AU$3:$AU$40, H75, 0)))

The filter works for everything but the wildcards. If I enter "#??" literally in cell H75, it passes. If I enter "#ie" in cell H75, it triggers an alert.

What am I doing wrong? (Excel 2010/Windows 7)

Thanks

the list includes: <table style="width: 64px; height: 418px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width:48pt" width="64"> </colgroup><tbody><tr style="mso-height-source:userset;height:13.15pt" height="17"> <td class="xl65" style="height:13.15pt;width:48pt" height="17" width="64">igh</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td style="height:13.15pt;border-top:none" height="17">a</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td style="height:13.15pt;border-top:none" height="17">ai</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td style="height:13.15pt;border-top:none" height="17">ay</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td style="height:13.15pt;border-top:none" height="17">au</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td style="height:13.15pt;border-top:none" height="17">e</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td style="height:13.15pt;border-top:none" height="17">ee</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td style="height:13.15pt;border-top:none" height="17">i</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td style="height:13.15pt;border-top:none" height="17">o</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td class="xl67" style="height:13.15pt;border-top:none" height="17">oa</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td style="height:13.15pt;border-top:none" height="17">oe</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td style="height:13.15pt;border-top:none" height="17">oi</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td style="height:13.15pt;border-top:none" height="17">#???</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td style="height:13.15pt;border-top:none" height="17">#??</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td style="height:13.15pt;border-top:none" height="17">oo</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td style="height:13.15pt;border-top:none" height="17">ou</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td class="xl67" style="height:13.15pt;border-top:none" height="17">oy</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td style="height:13.15pt;border-top:none" height="17">u</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td style="height:13.15pt;border-top:none" height="17">x</td> </tr> <tr style="mso-height-source:userset;height:12.75pt" height="17"> <td style="height:12.75pt;border-top:none" height="17">y</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td style="height:13.15pt;border-top:none" height="17">z

</td> </tr> </tbody></table>

 

[Aladin Akyurek]

Care to indicate which cell is data-validated and how?

 

[Marcelo Branco]

Maybe this

=OR(ISNUMBER(MATCH(H75,$AU$3:$AU$40,0)), SUM(IF(ISNUMBER(MATCH($AU$3:$AU$40,H75,0)),1)))

HTH

M.

 

[Audiology]

Marcello,

Perfect solution. Thank you.

 

[Marcelo Branco]

Marcello,

Perfect solution. Thank you.

You are welcome.

Tks for the feedback

M.

 

[Audiology]

New wrinkle:

The wild card validation fix worked fine (Excel 2003).

I have had need to re-enter the data using copy/paste from one workbook to another using two open versions of excel 2010.

The validation formula no longer works with wild cards.

 

[Audiology]

The formula passes "#??" and "#*" literally but does not accept "??" or "*" as wild cards.

 

[Audiology]

The problem was that the formula had to be re-entered with ctrl-shift-enter.