Hi

Sorry the Sheet5 part is just because I happened to be on Sheet5 of the test
workbook I was using. It has no relevance to the formulae for defining the
Names.

Regards

Roger Govier


Roger Govier wrote:
> Hi
>
> One way with a bit of a cheat.
> Define Names as Pos1, Pos2 .... Pos5
> =AND(CODE(MID(Sheet5!A1,1,1))>47,CODE(MID(Sheet5!A1,1,1)<58))
> for Pos1, change the A1,1,1 to A1,2,1 A1,3,1 etc as you define each
> successive name.
>
> In Data Validation>Custom
> =AND(LEN(A1)=5,Pos1,Pos2,Pos3,Pos4,Pos5)
>
>
> Regards
>
> Roger Govier
>
>
> Ron Coderre wrote:
>
>> Yeah, I tried playing with =AND(LEN(A1)=5,ISNUMBER(-A1)) and couldn't
>> get past: plus signs, minus signs, decimal points, commas, and fractions:
>>
>> +1234
>> -12.3
>> 1.234
>> 1,234
>> 2 1/2
>>
>> Consequently, I had to settle for my somewhat inelegant, but
>> functional, formula. Hopefully, a shorter method will turn up.
>>