Data Validation Rules

Louise

unread,

May 10, 2006, 7:07:02 AM5/10/06

to

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

aidan.h...@virgin.net

unread,

May 10, 2006, 7:27:05 AM5/10/06

to

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

Louise

unread,

May 10, 2006, 7:46:02 AM5/10/06

to

Hello, thank you for your reply.

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

Louise

unread,

May 10, 2006, 7:48:02 AM5/10/06

to

Found it! THanks again.
Louise

Bob Phillips

unread,

May 10, 2006, 7:52:22 AM5/10/06

to

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" <Lou...@discussions.microsoft.com> wrote in message
news:910BA467-A80D-4327...@microsoft.com...

Dav

unread,

May 10, 2006, 7:34:31 AM5/10/06

to

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

--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27107
View this thread: http://www.excelforum.com/showthread.php?threadid=540615

Louise

unread,

May 10, 2006, 8:02:02 AM5/10/06

to

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