Data Validation - Restriction

[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 it allows me to 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 restricted

Thanks for your help

--
Kevin

[Toppers]

Kevin,
In my testing (XL2003) I can only enter data from the list when
I select LIST as a DV option; anything else produces an error.

"Kevin" wrote:

> 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 it allows me to 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 restricted
>
> Thanks for your help
>
> --
> Kevin

[Kevin]

Hi - so I am selecting Allow = "list" also and then Data is greyed out and
then source "=OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)"

The list works fine but I can also enter any text i like but I want to
restrict to the list

any ideas?
--
Kevin


"Toppers" wrote:

> Kevin,
> In my testing (XL2003) I can only enter data from the list when
> I select LIST as a DV option; anything else produces an error.
>
> "Kevin" wrote:
>
> > 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 it allows me to 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 restricted
> >
> > Thanks for your help
> >
> > --
> > Kevin

[Ron Coderre]

Check a DV setting:

Data|Validation
Switch to the "Error Alert" tab
Is the "Show error alert..." box UNCHECKED"

If yes, the cell wil allow anything you type.

Does that help?

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

XL2002, WinXP-Pro


"Kevin" wrote:

> Hi - so I am selecting Allow = "list" also and then Data is greyed out and
> then source "=OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)"
>
> The list works fine but I can also enter any text i like but I want to
> restrict to the list
>
> any ideas?
> --
> Kevin
>
>
> "Toppers" wrote:
>
> > Kevin,
> > In my testing (XL2003) I can only enter data from the list when
> > I select LIST as a DV option; anything else produces an error.
> >
> > "Kevin" wrote:
> >
> > > 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 it allows me to 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 restricted
> > >
> > > Thanks for your help
> > >
> > > --
> > > Kevin

[Toppers]

Sorry .. none!

This was my DV:

"=OFFSET($A$1,1,0,COUNTA(A:A),1)"

Do you want to post sample file to me and I'll look at it?
(toppers@johntopley.fsnet.co.uk)

"Kevin" wrote:

> Hi - so I am selecting Allow = "list" also and then Data is greyed out and
> then source "=OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)"
>
> The list works fine but I can also enter any text i like but I want to
> restrict to the list
>
> any ideas?
> --
> Kevin
>
>
> "Toppers" wrote:
>
> > Kevin,
> > In my testing (XL2003) I can only enter data from the list when
> > I select LIST as a DV option; anything else produces an error.
> >
> > "Kevin" wrote:
> >
> > > 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 it allows me to 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 restricted
> > >
> > > Thanks for your help
> > >
> > > --
> > > Kevin

[Toppers]

Ron,
So obvious - flag is automatically set (in XL2003) so assumed
this to be the state!

"Ron Coderre" wrote:

> Check a DV setting:
>
> Data|Validation
> Switch to the "Error Alert" tab
> Is the "Show error alert..." box UNCHECKED"
>
> If yes, the cell wil allow anything you type.
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "Kevin" wrote:
>
> > Hi - so I am selecting Allow = "list" also and then Data is greyed out and
> > then source "=OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)"
> >
> > The list works fine but I can also enter any text i like but I want to
> > restrict to the list
> >
> > any ideas?
> > --
> > Kevin
> >
> >
> > "Toppers" wrote:
> >
> > > Kevin,
> > > In my testing (XL2003) I can only enter data from the list when
> > > I select LIST as a DV option; anything else produces an error.
> > >
> > > "Kevin" wrote:
> > >
> > > > 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 it allows me to 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 restricted
> > > >
> > > > Thanks for your help
> > > >
> > > > --
> > > > Kevin

[Kevin]

hi - error alert did not help

I tired using your formula below and it works exactly how I would like it -
therefore the issues appears to be because I am using a reference (eg
"RegionStart" instead of "$A1$") and/or the reference is contained on a
different sheet within the workbook

any thoughts

--
Kevin


"Toppers" wrote:

> Sorry .. none!
>
> This was my DV:
>
> "=OFFSET($A$1,1,0,COUNTA(A:A),1)"
>
> Do you want to post sample file to me and I'll look at it?
> (toppers@johntopley.fsnet.co.uk)
>
> "Kevin" wrote:
>
> > Hi - so I am selecting Allow = "list" also and then Data is greyed out and
> > then source "=OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)"
> >
> > The list works fine but I can also enter any text i like but I want to
> > restrict to the list
> >
> > any ideas?
> > --
> > Kevin
> >
> >
> > "Toppers" wrote:
> >
> > > Kevin,
> > > In my testing (XL2003) I can only enter data from the list when
> > > I select LIST as a DV option; anything else produces an error.
> > >
> > > "Kevin" wrote:
> > >
> > > > 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 it allows me to 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 restricted
> > > >
> > > > Thanks for your help
> > > >
> > > > --
> > > > Kevin

[Toppers]

Kevin,
My testing produced the same results as yours i.e. works if
$A$1 but not with the named variable. What is RegionColumn?

"Kevin" wrote:

> hi - error alert did not help
>
> I tired using your formula below and it works exactly how I would like it -
> therefore the issues appears to be because I am using a reference (eg
> "RegionStart" instead of "$A1$") and/or the reference is contained on a
> different sheet within the workbook
>
> any thoughts
>
> --
> Kevin
>
>
> "Toppers" wrote:
>
> > Sorry .. none!
> >
> > This was my DV:
> >
> > "=OFFSET($A$1,1,0,COUNTA(A:A),1)"
> >
> > Do you want to post sample file to me and I'll look at it?
> > (toppers@johntopley.fsnet.co.uk)
> >
> > "Kevin" wrote:
> >
> > > Hi - so I am selecting Allow = "list" also and then Data is greyed out and
> > > then source "=OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)"
> > >
> > > The list works fine but I can also enter any text i like but I want to
> > > restrict to the list
> > >
> > > any ideas?
> > > --
> > > Kevin
> > >
> > >
> > > "Toppers" wrote:
> > >
> > > > Kevin,
> > > > In my testing (XL2003) I can only enter data from the list when
> > > > I select LIST as a DV option; anything else produces an error.
> > > >
> > > > "Kevin" wrote:
> > > >
> > > > > 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 it allows me to 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 restricted
> > > > >
> > > > > Thanks for your help
> > > > >
> > > > > --
> > > > > Kevin