Data Validation Option

[Graham Haughs]

Is it possible to have a data validation list for entry in a cell but also
be able to enter any value you want in the cell? I have seen Debra
Dalgleish's files where you can enter any value and it is added to the list.
Ideally I would like to have the facility to do this but I do not want to
add this to the list as it is a fixed list. I know this may sound peculiar
but it is a solution to a particular situation. It would mean the user has
the option of entering a value from the drop down list or entering his or
her own value. On returning to this cell the options would remain the same
with the list as it was originally.
I would value any guidance.

Kind regards,
Graham Haughs
Turriff
Scotland

 

[Gord Dibben]

Graham

Just set up your DV list as normal.

On the Error Alert tab, uncheck the "show error alert etc." option.

You can now type in the cell but that will not be added to the list.

Gord Dibben Excel MVP

 

[Dave Peterson]

There's a tab on the Data|Validation dialog called: Error Alert.

You can make it so you accept the typed value with a warning or just accept it.
(or not accept it).

 

[Graham Haughs]

Thanks to you both. I have been looking at so many wierd and wonderful
options I couldn't see this solution. I suppose it is like everything else
though, it is only simple if you know the answer.
Many thanks.

Graham

 

[RagDyeR]

Just to add another option:

Simply add a blank row anywhere within your list (preferably at the bottom),
and the user will have the ability to add their own data, even though the
error alert is *still* checked.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Thanks to you both. I have been looking at so many wierd and wonderful
options I couldn't see this solution. I suppose it is like everything else
though, it is only simple if you know the answer.
Many thanks.

Graham