data validation  
Author Message
windsim





PostPosted: Wed May 24 08:32:07 CDT 2006 Top

Excel >> data validation

Hi
using the Data validation function, is it possible to check people entering
into a cell telephone numbers that start 07 or 09

Otherwise what's the best way to do this please

thanks
Al

Excel216  
 
 
Andy>





PostPosted: Wed May 24 08:32:07 CDT 2006 Top

Excel >> data validation Hi
Format your cell as text first (otherwise you'll lose your leading 0s) and
then use a custom validation of:
=OR(LEFT(A2,2)="07",LEFT(A2,2)="09")

Hope this helps.
Andy.



> Hi
> using the Data validation function, is it possible to check people
> entering into a cell telephone numbers that start 07 or 09
>
> Otherwise what's the best way to do this please
>
> thanks
> Al
>
>


 
 
P





PostPosted: Wed May 24 08:58:03 CDT 2006 Top

Excel >> data validation Hi

I am a new user, and want to use Excel Data Validation within a column to
restrict the entry of phone numbers beginning with 07 and 09, I have tried
Andy's fix but it does not appear to work.

I would very much appreciate some help.

Thank you
--
P




> Hi
> Format your cell as text first (otherwise you'll lose your leading 0s) and
> then use a custom validation of:
> =OR(LEFT(A2,2)="07",LEFT(A2,2)="09")
>
> Hope this helps.
> Andy.
>


> > Hi
> > using the Data validation function, is it possible to check people
> > entering into a cell telephone numbers that start 07 or 09
> >
> > Otherwise what's the best way to do this please
> >
> > thanks
> > Al
> >
> >
>
>
>
 
 
Andy>





PostPosted: Wed May 24 09:15:20 CDT 2006 Top

Excel >> data validation Hi
It works for me!
Select A2, select Data/Validation. In the Allow box select Custom. In the
Formula box type:
=OR(LEFT(A2,2)="07",LEFT(A2,2)="09")

What results do you get when you try it?

Andy.



> Hi
>
> I am a new user, and want to use Excel Data Validation within a column to
> restrict the entry of phone numbers beginning with 07 and 09, I have tried
> Andy's fix but it does not appear to work.
>
> I would very much appreciate some help.
>
> Thank you
> --
> P
>
>

>
>> Hi
>> Format your cell as text first (otherwise you'll lose your leading 0s)
>> and
>> then use a custom validation of:
>> =OR(LEFT(A2,2)="07",LEFT(A2,2)="09")
>>
>> Hope this helps.
>> Andy.
>>


>> > Hi
>> > using the Data validation function, is it possible to check people
>> > entering into a cell telephone numbers that start 07 or 09
>> >
>> > Otherwise what's the best way to do this please
>> >
>> > thanks
>> > Al
>> >
>> >
>>
>>
>>


 
 
P





PostPosted: Wed May 24 10:21:03 CDT 2006 Top

Excel >> data validation Hi Andy

Thank you very much for your reply, very much appreciated.

I have an existing telephone list that does not include any of the numbers I
want blocked, I live in the UK and the usaual format for phone numbers is a
five digit area code followed by a six digit number e.g. 01752 216547. All
numbers start with a zero.

Because of this, I want all zero prefixes to be allowed, except 07 and 09
which are mobiles and premium rates.

I highlighted the column, selected Data then Validation, and entered your
formula as suggested using the first available cell (D2 in my case) for the
formula, but when I try to enter in a restriced 07 prefix it allows me to do
so.

Thank you once again,

Paul




> Hi
> It works for me!
> Select A2, select Data/Validation. In the Allow box select Custom. In the
> Formula box type:
> =OR(LEFT(A2,2)="07",LEFT(A2,2)="09")
>
> What results do you get when you try it?
>
> Andy.
>


> > Hi
> >
> > I am a new user, and want to use Excel Data Validation within a column to
> > restrict the entry of phone numbers beginning with 07 and 09, I have tried
> > Andy's fix but it does not appear to work.
> >
> > I would very much appreciate some help.
> >
> > Thank you
> > --
> > P
> >
> >

> >
> >> Hi
> >> Format your cell as text first (otherwise you'll lose your leading 0s)
> >> and
> >> then use a custom validation of:
> >> =OR(LEFT(A2,2)="07",LEFT(A2,2)="09")
> >>
> >> Hope this helps.
> >> Andy.
> >>


> >> > Hi
> >> > using the Data validation function, is it possible to check people
> >> > entering into a cell telephone numbers that start 07 or 09
> >> >
> >> > Otherwise what's the best way to do this please
> >> >
> >> > thanks
> >> > Al
> >> >
> >> >
> >>
> >>
> >>
>
>
>
 
 
Roger





PostPosted: Wed May 24 16:20:41 CDT 2006 Top

Excel >> data validation Hi Paul

You need to wrap Andy's formula in a NOT() function to exclude them
=NOT(OR(LEFT(A2,2)="07",LEFT(A2,2)="09"))
Don't forget to format as Text first as Andy says, otherwise you loose
the leading zero.

--
Regards

Roger Govier




> Hi Andy
>
> Thank you very much for your reply, very much appreciated.
>
> I have an existing telephone list that does not include any of the
> numbers I
> want blocked, I live in the UK and the usaual format for phone numbers
> is a
> five digit area code followed by a six digit number e.g. 01752 216547.
> All
> numbers start with a zero.
>
> Because of this, I want all zero prefixes to be allowed, except 07 and
> 09
> which are mobiles and premium rates.
>
> I highlighted the column, selected Data then Validation, and entered
> your
> formula as suggested using the first available cell (D2 in my case)
> for the
> formula, but when I try to enter in a restriced 07 prefix it allows me
> to do
> so.
>
> Thank you once again,
>
> Paul
>
>

>
>> Hi
>> It works for me!
>> Select A2, select Data/Validation. In the Allow box select Custom. In
>> the
>> Formula box type:
>> =OR(LEFT(A2,2)="07",LEFT(A2,2)="09")
>>
>> What results do you get when you try it?
>>
>> Andy.
>>


>> > Hi
>> >
>> > I am a new user, and want to use Excel Data Validation within a
>> > column to
>> > restrict the entry of phone numbers beginning with 07 and 09, I
>> > have tried
>> > Andy's fix but it does not appear to work.
>> >
>> > I would very much appreciate some help.
>> >
>> > Thank you
>> > --
>> > P
>> >
>> >

>> >
>> >> Hi
>> >> Format your cell as text first (otherwise you'll lose your leading
>> >> 0s)
>> >> and
>> >> then use a custom validation of:
>> >> =OR(LEFT(A2,2)="07",LEFT(A2,2)="09")
>> >>
>> >> Hope this helps.
>> >> Andy.
>> >>


>> >> > Hi
>> >> > using the Data validation function, is it possible to check
>> >> > people
>> >> > entering into a cell telephone numbers that start 07 or 09
>> >> >
>> >> > Otherwise what's the best way to do this please
>> >> >
>> >> > thanks
>> >> > Al
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>


 
 
P





PostPosted: Wed May 24 19:26:01 CDT 2006 Top

Excel >> data validation Thank you very much Roger, could not make the formula work as Andy has
written it. Will test your suggestion soon.

Thank you once again Andy and Roger.
--
P




> Hi Paul
>
> You need to wrap Andy's formula in a NOT() function to exclude them
> =NOT(OR(LEFT(A2,2)="07",LEFT(A2,2)="09"))
> Don't forget to format as Text first as Andy says, otherwise you loose
> the leading zero.
>
> --
> Regards
>
> Roger Govier
>
>


> > Hi Andy
> >
> > Thank you very much for your reply, very much appreciated.
> >
> > I have an existing telephone list that does not include any of the
> > numbers I
> > want blocked, I live in the UK and the usaual format for phone numbers
> > is a
> > five digit area code followed by a six digit number e.g. 01752 216547.
> > All
> > numbers start with a zero.
> >
> > Because of this, I want all zero prefixes to be allowed, except 07 and
> > 09
> > which are mobiles and premium rates.
> >
> > I highlighted the column, selected Data then Validation, and entered
> > your
> > formula as suggested using the first available cell (D2 in my case)
> > for the
> > formula, but when I try to enter in a restriced 07 prefix it allows me
> > to do
> > so.
> >
> > Thank you once again,
> >
> > Paul
> >
> >

> >
> >> Hi
> >> It works for me!
> >> Select A2, select Data/Validation. In the Allow box select Custom. In
> >> the
> >> Formula box type:
> >> =OR(LEFT(A2,2)="07",LEFT(A2,2)="09")
> >>
> >> What results do you get when you try it?
> >>
> >> Andy.
> >>


> >> > Hi
> >> >
> >> > I am a new user, and want to use Excel Data Validation within a
> >> > column to
> >> > restrict the entry of phone numbers beginning with 07 and 09, I
> >> > have tried
> >> > Andy's fix but it does not appear to work.
> >> >
> >> > I would very much appreciate some help.
> >> >
> >> > Thank you
> >> > --
> >> > P
> >> >
> >> >

> >> >
> >> >> Hi
> >> >> Format your cell as text first (otherwise you'll lose your leading
> >> >> 0s)
> >> >> and
> >> >> then use a custom validation of:
> >> >> =OR(LEFT(A2,2)="07",LEFT(A2,2)="09")
> >> >>
> >> >> Hope this helps.
> >> >> Andy.
> >> >>


> >> >> > Hi
> >> >> > using the Data validation function, is it possible to check
> >> >> > people
> >> >> > entering into a cell telephone numbers that start 07 or 09
> >> >> >
> >> >> > Otherwise what's the best way to do this please
> >> >> >
> >> >> > thanks
> >> >> > Al
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>