Hi

I want to use data validation and have been looking at the website
www.contextures.com

From here I have used the following formula in my data validation / source
to link to a list and update automatically as new entires to my list are
added / deleted

=OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)

The problem is I can still enter values not in the list (there are no
blank spaces when I select the list) and I would like to restrict this so
that only values on the list can be entered

Through help on this forum I have figured the issue is most likely related
to the fact that above formula refers to a named range (eg "RegionStart")
rather than specific cells (eg $A$1). Any idea why this is or other options
for me

thanks
--
Kevin

Hi Kevin

I would think the most likely cause is that the Error Alert box is not
checked on the third tab of the Data Validation setup dialogue.

--
Regards

Roger Govier


"Kevin" wrote in message
...
Hi

I want to use data validation and have been looking at the website
www.contextures.com

From here I have used the following formula in my data validation /
source
to link to a list and update automatically as new entires to my list
are
added / deleted

=OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)

The problem is I can still enter values not in the list (there are no
blank spaces when I select the list) and I would like to restrict this
so
that only values on the list can be entered

Through help on this forum I have figured the issue is most likely
related
to the fact that above formula refers to a named range (eg
"RegionStart")
rather than specific cells (eg $A$1). Any idea why this is or other
options
for me

thanks
--
Kevin

sorry should have stated somebody pointed this out but this does not fix my
problem as it was already checked - I even changed the style from "Warning"
to "Stop".

Have done more testing -the problem seems to be that the reference of my
named range contains blank cells. so my formula below excludes these when I
look in the drop down list but I think it still allows for the fact that
there are blank cells in the range and hence allows me add anything

any solutions to this?
--
Kevin


"Roger Govier" wrote:

Hi Kevin

I would think the most likely cause is that the Error Alert box is not
checked on the third tab of the Data Validation setup dialogue.

--
Regards

Roger Govier


"Kevin" wrote in message
...
Hi

I want to use data validation and have been looking at the website
www.contextures.com

From here I have used the following formula in my data validation /
source
to link to a list and update automatically as new entires to my list
are
added / deleted

=OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)

The problem is I can still enter values not in the list (there are no
blank spaces when I select the list) and I would like to restrict this
so
that only values on the list can be entered

Through help on this forum I have figured the issue is most likely
related
to the fact that above formula refers to a named range (eg
"RegionStart")
rather than specific cells (eg $A$1). Any idea why this is or other
options
for me

thanks
--
Kevin