I have an array validation formula:
=OR(ISNUMBER(MATCH(H5,$AU$3:$AU$40,0)), SUM(IF(ISNUMBER(MATCH($AU$3:$AU$40,H5,0)),1)))
Which should compare the entry into cell (H5), etc, with the following list of letter combinations and reject outliers. The exception is #?? which should allow the entry of "#" and any combination of two vowels NOT on the list, below.
<table border="0" cellpadding="0" cellspacing="0" width="64"><colgroup><col style="width:48pt" width="64"> </colgroup><tbody><tr style="mso-height-source:userset;height:13.15pt" height="17"> <td class="xl66" style="height:13.15pt; width:48pt" align="left" height="17" width="64">igh</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td class="xl72" style="height:13.15pt;border-top:none" align="left" height="17">a</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td class="xl72" style="height:13.15pt;border-top:none" align="left" height="17">ai</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td class="xl72" style="height:13.15pt;border-top:none" align="left" height="17">ay</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td style="height:13.15pt;border-top:none" align="left" height="17">au</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td class="xl72" style="height:13.15pt;border-top:none" align="left" height="17">e</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td class="xl72" style="height:13.15pt;border-top:none" align="left" height="17">ee</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td class="xl72" style="height:13.15pt;border-top:none" align="left" height="17">i</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td class="xl72" style="height:13.15pt;border-top:none" align="left" height="17">o</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td class="xl73" style="height:13.15pt;border-top:none" align="left" height="17">oa</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td class="xl72" style="height:13.15pt;border-top:none" align="left" height="17">oe</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td class="xl72" style="height:13.15pt;border-top:none" align="left" height="17">oi</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl72" style="height:14.25pt;border-top:none" align="left" height="19">#??</td> </tr> <tr style="height:13.5pt" height="18"> <td class="xl72" style="height:13.5pt;border-top:none" align="left" height="18">oo</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl72" style="height:12.75pt;border-top:none" align="left" height="17">ou</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl73" style="height:12.75pt;border-top:none" align="left" height="17">oy</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl72" style="height:12.75pt;border-top:none" align="left" height="17">u</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl72" style="height:12.75pt;border-top:none" align="left" height="17">x</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl72" style="height:12.75pt;border-top:none" align="left" height="17">y</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl72" style="height:12.75pt;border-top:none" align="left" height="17">z</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl76" style="height:12.75pt" align="left" height="17">ew</td> </tr> </tbody></table>
I enter the formula in the validation setup box using ctrl-shft-enter. When I protect the worksheet and save the workbook as a template, everything works at that time. The next time the template is used, however, the "wildcard "#??" no longer functions.. If I reenter the validation formula, it works again - just once.
What am I doing wrong???? I would like to save this validation scheme in a protected worksheet to a .xltm file.
HELP!
=OR(ISNUMBER(MATCH(H5,$AU$3:$AU$40,0)), SUM(IF(ISNUMBER(MATCH($AU$3:$AU$40,H5,0)),1)))
Which should compare the entry into cell (H5), etc, with the following list of letter combinations and reject outliers. The exception is #?? which should allow the entry of "#" and any combination of two vowels NOT on the list, below.
<table border="0" cellpadding="0" cellspacing="0" width="64"><colgroup><col style="width:48pt" width="64"> </colgroup><tbody><tr style="mso-height-source:userset;height:13.15pt" height="17"> <td class="xl66" style="height:13.15pt; width:48pt" align="left" height="17" width="64">igh</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td class="xl72" style="height:13.15pt;border-top:none" align="left" height="17">a</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td class="xl72" style="height:13.15pt;border-top:none" align="left" height="17">ai</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td class="xl72" style="height:13.15pt;border-top:none" align="left" height="17">ay</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td style="height:13.15pt;border-top:none" align="left" height="17">au</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td class="xl72" style="height:13.15pt;border-top:none" align="left" height="17">e</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td class="xl72" style="height:13.15pt;border-top:none" align="left" height="17">ee</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td class="xl72" style="height:13.15pt;border-top:none" align="left" height="17">i</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td class="xl72" style="height:13.15pt;border-top:none" align="left" height="17">o</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td class="xl73" style="height:13.15pt;border-top:none" align="left" height="17">oa</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td class="xl72" style="height:13.15pt;border-top:none" align="left" height="17">oe</td> </tr> <tr style="mso-height-source:userset;height:13.15pt" height="17"> <td class="xl72" style="height:13.15pt;border-top:none" align="left" height="17">oi</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl72" style="height:14.25pt;border-top:none" align="left" height="19">#??</td> </tr> <tr style="height:13.5pt" height="18"> <td class="xl72" style="height:13.5pt;border-top:none" align="left" height="18">oo</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl72" style="height:12.75pt;border-top:none" align="left" height="17">ou</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl73" style="height:12.75pt;border-top:none" align="left" height="17">oy</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl72" style="height:12.75pt;border-top:none" align="left" height="17">u</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl72" style="height:12.75pt;border-top:none" align="left" height="17">x</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl72" style="height:12.75pt;border-top:none" align="left" height="17">y</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl72" style="height:12.75pt;border-top:none" align="left" height="17">z</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl76" style="height:12.75pt" align="left" height="17">ew</td> </tr> </tbody></table>
I enter the formula in the validation setup box using ctrl-shft-enter. When I protect the worksheet and save the workbook as a template, everything works at that time. The next time the template is used, however, the "wildcard "#??" no longer functions.. If I reenter the validation formula, it works again - just once.
What am I doing wrong???? I would like to save this validation scheme in a protected worksheet to a .xltm file.
HELP!