Data Validation Rules

Hi all

I am trying to think of occasions when I would use the Data Validation Rules
feature.

The only one I can think of at the moment is that if I had a new user to
Excel, I can set validation rules per column, only allowing them to enter
figures between, say, 100 and 200. It will, therefore, highlight any figures
outside these parameters that they have entered by mistake.

Can anybody think of another typical example when Data Validation would be
useful? What do other people use it for?

Thank you.
Louise

If you are entering data where it is important that a persons name is entered conistently
eg Dave Smith, David Smith, Mr D Smith, Mr David Smith etc all all the same person, you can only give options of the valid names. This would be useful if you were to be running a pivot table or countif, sumif or sumproduct on the data to aggregate it. As a person would be called by the same name every time!

So in the above example you would have one unique name for each member of staff and the list to validate against would be the list of staff.

Also you sometimes wish to have a date always in the past, eg you can not fill out expenses claims before expenses have been incurred!

Regards

Dav

Any manner of reasons
(1) Allow a choice from a list
(2) restrict entry to ONLY dates - thus stopping people typing 01.01.01
and thinking it's a date
(3) Restrict the length of entry - for many of my applications, a value
entered must always be a given length
(4) even without a rule, Data Validation allows you to give
instructions to the user

Hello, thank you for your reply.

What do you mean 'allow a choice from a list'?

Louise

"[email protected]" wrote:

> Any manner of reasons
> (1) Allow a choice from a list
> (2) restrict entry to ONLY dates - thus stopping people typing 01.01.01
> and thinking it's a date
> (3) Restrict the length of entry - for many of my applications, a value
> entered must always be a given length
> (4) even without a rule, Data Validation allows you to give
> instructions to the user
>
>

Found it! THanks again.
Louise

"Louise" wrote:

> Hello, thank you for your reply.
>
> What do you mean 'allow a choice from a list'?
>
> Louise
>
> "[email protected]" wrote:
>
> > Any manner of reasons
> > (1) Allow a choice from a list
> > (2) restrict entry to ONLY dates - thus stopping people typing 01.01.01
> > and thinking it's a date
> > (3) Restrict the length of entry - for many of my applications, a value
> > entered must always be a given length
> > (4) even without a rule, Data Validation allows you to give
> > instructions to the user
> >
> >

If you go into Data Validation, the first dropdown, named Allow, provides
the types of validation that you can set. If you choose List, you can refer
to a list of allowable values, or even type them in directly.

A better question is why not use Data Validation. It is a great facility,
and should be used more. Debra Dalgleish has details on her website at
http://www.contextures.com/xlDataVal01.html

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Louise" <[email protected]> wrote in message
news:[email protected]...
> Hello, thank you for your reply.
>
> What do you mean 'allow a choice from a list'?
>
> Louise
>
> "[email protected]" wrote:
>
> > Any manner of reasons
> > (1) Allow a choice from a list
> > (2) restrict entry to ONLY dates - thus stopping people typing 01.01.01
> > and thinking it's a date
> > (3) Restrict the length of entry - for many of my applications, a value
> > entered must always be a given length
> > (4) even without a rule, Data Validation allows you to give
> > instructions to the user
> >
> >

Yes, you're quite right, I don't think I'm using it to the best of its ability.
Thanks for the link.
Louise

"Bob Phillips" wrote:

> If you go into Data Validation, the first dropdown, named Allow, provides
> the types of validation that you can set. If you choose List, you can refer
> to a list of allowable values, or even type them in directly.
>
> A better question is why not use Data Validation. It is a great facility,
> and should be used more. Debra Dalgleish has details on her website at
> http://www.contextures.com/xlDataVal01.html
>
> --
> HTH
>
> Bob Phillips
>
> (remove xxx from email address if mailing direct)
>
> "Louise" <[email protected]> wrote in message
> news:[email protected]...
> > Hello, thank you for your reply.
> >
> > What do you mean 'allow a choice from a list'?
> >
> > Louise
> >
> > "[email protected]" wrote:
> >
> > > Any manner of reasons
> > > (1) Allow a choice from a list
> > > (2) restrict entry to ONLY dates - thus stopping people typing 01.01.01
> > > and thinking it's a date
> > > (3) Restrict the length of entry - for many of my applications, a value
> > > entered must always be a given length
> > > (4) even without a rule, Data Validation allows you to give
> > > instructions to the user
> > >
> > >
>
>
>