I am trying to set up a spreadsheet so that in particular cells the option is
available to pick from a list or enter freeform text. I thought I had found
the answer in the data validation help file, but this does not seem to make
any difference. Have I misunderstood what this means?

"To specify how you want to handle blank (null) values, select or clear the
Ignore blank check box.
Note: If your allowed values are based on a cell range with a defined name,
and there is a blank cell anywhere in the range, setting the Ignore blank
check box allows any values to be entered in the validated cell. This is also
true for any cells referenced by validation formulas: if any referenced cell
is blank, setting the Ignore blank check box allows any values to be entered
in the validated cell."

Maybe this option will work for you:

In the Data Validation dialog, set the Error Alert to "Warning" and enter an
appropriate message (like: That item is not on the list...do you want to add
it?)

If the users enter non-list entries, they will be notified of the variance,
but allowed to use the new value.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"PJH" wrote:

I am trying to set up a spreadsheet so that in particular cells the option is
available to pick from a list or enter freeform text. I thought I had found
the answer in the data validation help file, but this does not seem to make
any difference. Have I misunderstood what this means?

"To specify how you want to handle blank (null) values, select or clear the
Ignore blank check box.
Note: If your allowed values are based on a cell range with a defined name,
and there is a blank cell anywhere in the range, setting the Ignore blank
check box allows any values to be entered in the validated cell. This is also
true for any cells referenced by validation formulas: if any referenced cell
is blank, setting the Ignore blank check box allows any values to be entered
in the validated cell."

There are instructions here for creating a data validation list based on a
named range:

http://www.contextures.com/xlDataVal01.html

If one of the cells in the named range is blank, and 'Ignore blank' has a
check mark, users will be able to select from the dropdown list, or type any
value in the cell.

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

"PJH" wrote in message
...
I am trying to set up a spreadsheet so that in particular cells the option
is
available to pick from a list or enter freeform text. I thought I had
found
the answer in the data validation help file, but this does not seem to
make
any difference. Have I misunderstood what this means?

"To specify how you want to handle blank (null) values, select or clear
the
Ignore blank check box.
Note: If your allowed values are based on a cell range with a defined
name,
and there is a blank cell anywhere in the range, setting the Ignore blank
check box allows any values to be entered in the validated cell. This is
also
true for any cells referenced by validation formulas: if any referenced
cell
is blank, setting the Ignore blank check box allows any values to be
entered
in the validated cell."