Data Validation

[Kevin]

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

[Roger Govier]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>