software

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,
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.

quot;Kevinquot; wrote:

gt; Hi
gt;
gt; I want to use data validation and have been looking at the website
gt; www.contextures.com
gt;
gt; From here I have used the following formula in my data validation / source
gt; to link to a list and update automatically as new entires to my list are
gt; added / deleted
gt;
gt; =OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)
gt;
gt; The problem is it allows me to enter values not in the list (there are no
gt; blank spaces when I select the list) and I would like to restrict this so
gt; that only values on the list can be restricted
gt;
gt; Thanks for your help
gt;
gt; --
gt; Kevin

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

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

any ideas?
--
Kevinquot;Toppersquot; wrote:

gt; Kevin,
gt; In my testing (XL2003) I can only enter data from the list when
gt; I select LIST as a DV option; anything else produces an error.
gt;
gt; quot;Kevinquot; wrote:
gt;
gt; gt; Hi
gt; gt;
gt; gt; I want to use data validation and have been looking at the website
gt; gt; www.contextures.com
gt; gt;
gt; gt; From here I have used the following formula in my data validation / source
gt; gt; to link to a list and update automatically as new entires to my list are
gt; gt; added / deleted
gt; gt;
gt; gt; =OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)
gt; gt;
gt; gt; The problem is it allows me to enter values not in the list (there are no
gt; gt; blank spaces when I select the list) and I would like to restrict this so
gt; gt; that only values on the list can be restricted
gt; gt;
gt; gt; Thanks for your help
gt; gt;
gt; gt; --
gt; gt; Kevin

Check a DV setting:

Data|Validation
Switch to the quot;Error Alertquot; tab
Is the quot;Show error alert...quot; box UNCHECKEDquot;

If yes, the cell wil allow anything you type.

Does that help?

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

XL2002, WinXP-Proquot;Kevinquot; wrote:

gt; Hi - so I am selecting Allow = quot;listquot; also and then Data is greyed out and
gt; then source quot;=OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)quot;
gt;
gt; The list works fine but I can also enter any text i like but I want to
gt; restrict to the list
gt;
gt; any ideas?
gt; --
gt; Kevin
gt;
gt;
gt; quot;Toppersquot; wrote:
gt;
gt; gt; Kevin,
gt; gt; In my testing (XL2003) I can only enter data from the list when
gt; gt; I select LIST as a DV option; anything else produces an error.
gt; gt;
gt; gt; quot;Kevinquot; wrote:
gt; gt;
gt; gt; gt; Hi
gt; gt; gt;
gt; gt; gt; I want to use data validation and have been looking at the website
gt; gt; gt; www.contextures.com
gt; gt; gt;
gt; gt; gt; From here I have used the following formula in my data validation / source
gt; gt; gt; to link to a list and update automatically as new entires to my list are
gt; gt; gt; added / deleted
gt; gt; gt;
gt; gt; gt; =OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)
gt; gt; gt;
gt; gt; gt; The problem is it allows me to enter values not in the list (there are no
gt; gt; gt; blank spaces when I select the list) and I would like to restrict this so
gt; gt; gt; that only values on the list can be restricted
gt; gt; gt;
gt; gt; gt; Thanks for your help
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Kevin

Sorry .. none!

This was my DV:

quot;=OFFSET($A$1,1,0,COUNTA(A:A),1)quot;

Do you want to post sample file to me and I'll look at it?
)

quot;Kevinquot; wrote:

gt; Hi - so I am selecting Allow = quot;listquot; also and then Data is greyed out and
gt; then source quot;=OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)quot;
gt;
gt; The list works fine but I can also enter any text i like but I want to
gt; restrict to the list
gt;
gt; any ideas?
gt; --
gt; Kevin
gt;
gt;
gt; quot;Toppersquot; wrote:
gt;
gt; gt; Kevin,
gt; gt; In my testing (XL2003) I can only enter data from the list when
gt; gt; I select LIST as a DV option; anything else produces an error.
gt; gt;
gt; gt; quot;Kevinquot; wrote:
gt; gt;
gt; gt; gt; Hi
gt; gt; gt;
gt; gt; gt; I want to use data validation and have been looking at the website
gt; gt; gt; www.contextures.com
gt; gt; gt;
gt; gt; gt; From here I have used the following formula in my data validation / source
gt; gt; gt; to link to a list and update automatically as new entires to my list are
gt; gt; gt; added / deleted
gt; gt; gt;
gt; gt; gt; =OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)
gt; gt; gt;
gt; gt; gt; The problem is it allows me to enter values not in the list (there are no
gt; gt; gt; blank spaces when I select the list) and I would like to restrict this so
gt; gt; gt; that only values on the list can be restricted
gt; gt; gt;
gt; gt; gt; Thanks for your help
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Kevin

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

quot;Ron Coderrequot; wrote:

gt; Check a DV setting:
gt;
gt; Data|Validation
gt; Switch to the quot;Error Alertquot; tab
gt; Is the quot;Show error alert...quot; box UNCHECKEDquot;
gt;
gt; If yes, the cell wil allow anything you type.
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Kevinquot; wrote:
gt;
gt; gt; Hi - so I am selecting Allow = quot;listquot; also and then Data is greyed out and
gt; gt; then source quot;=OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)quot;
gt; gt;
gt; gt; The list works fine but I can also enter any text i like but I want to
gt; gt; restrict to the list
gt; gt;
gt; gt; any ideas?
gt; gt; --
gt; gt; Kevin
gt; gt;
gt; gt;
gt; gt; quot;Toppersquot; wrote:
gt; gt;
gt; gt; gt; Kevin,
gt; gt; gt; In my testing (XL2003) I can only enter data from the list when
gt; gt; gt; I select LIST as a DV option; anything else produces an error.
gt; gt; gt;
gt; gt; gt; quot;Kevinquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Hi
gt; gt; gt; gt;
gt; gt; gt; gt; I want to use data validation and have been looking at the website
gt; gt; gt; gt; www.contextures.com
gt; gt; gt; gt;
gt; gt; gt; gt; From here I have used the following formula in my data validation / source
gt; gt; gt; gt; to link to a list and update automatically as new entires to my list are
gt; gt; gt; gt; added / deleted
gt; gt; gt; gt;
gt; gt; gt; gt; =OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)
gt; gt; gt; gt;
gt; gt; gt; gt; The problem is it allows me to enter values not in the list (there are no
gt; gt; gt; gt; blank spaces when I select the list) and I would like to restrict this so
gt; gt; gt; gt; that only values on the list can be restricted
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks for your help
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; 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
quot;RegionStartquot; instead of quot;$A1$quot;) and/or the reference is contained on a
different sheet within the workbook

any thoughts

--
Kevinquot;Toppersquot; wrote:

gt; Sorry .. none!
gt;
gt; This was my DV:
gt;
gt; quot;=OFFSET($A$1,1,0,COUNTA(A:A),1)quot;
gt;
gt; Do you want to post sample file to me and I'll look at it?
gt; )
gt;
gt; quot;Kevinquot; wrote:
gt;
gt; gt; Hi - so I am selecting Allow = quot;listquot; also and then Data is greyed out and
gt; gt; then source quot;=OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)quot;
gt; gt;
gt; gt; The list works fine but I can also enter any text i like but I want to
gt; gt; restrict to the list
gt; gt;
gt; gt; any ideas?
gt; gt; --
gt; gt; Kevin
gt; gt;
gt; gt;
gt; gt; quot;Toppersquot; wrote:
gt; gt;
gt; gt; gt; Kevin,
gt; gt; gt; In my testing (XL2003) I can only enter data from the list when
gt; gt; gt; I select LIST as a DV option; anything else produces an error.
gt; gt; gt;
gt; gt; gt; quot;Kevinquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Hi
gt; gt; gt; gt;
gt; gt; gt; gt; I want to use data validation and have been looking at the website
gt; gt; gt; gt; www.contextures.com
gt; gt; gt; gt;
gt; gt; gt; gt; From here I have used the following formula in my data validation / source
gt; gt; gt; gt; to link to a list and update automatically as new entires to my list are
gt; gt; gt; gt; added / deleted
gt; gt; gt; gt;
gt; gt; gt; gt; =OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)
gt; gt; gt; gt;
gt; gt; gt; gt; The problem is it allows me to enter values not in the list (there are no
gt; gt; gt; gt; blank spaces when I select the list) and I would like to restrict this so
gt; gt; gt; gt; that only values on the list can be restricted
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks for your help
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; Kevin

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

quot;Kevinquot; wrote:

gt; hi - error alert did not help
gt;
gt; I tired using your formula below and it works exactly how I would like it -
gt; therefore the issues appears to be because I am using a reference (eg
gt; quot;RegionStartquot; instead of quot;$A1$quot;) and/or the reference is contained on a
gt; different sheet within the workbook
gt;
gt; any thoughts
gt;
gt; --
gt; Kevin
gt;
gt;
gt; quot;Toppersquot; wrote:
gt;
gt; gt; Sorry .. none!
gt; gt;
gt; gt; This was my DV:
gt; gt;
gt; gt; quot;=OFFSET($A$1,1,0,COUNTA(A:A),1)quot;
gt; gt;
gt; gt; Do you want to post sample file to me and I'll look at it?
gt; gt; )
gt; gt;
gt; gt; quot;Kevinquot; wrote:
gt; gt;
gt; gt; gt; Hi - so I am selecting Allow = quot;listquot; also and then Data is greyed out and
gt; gt; gt; then source quot;=OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)quot;
gt; gt; gt;
gt; gt; gt; The list works fine but I can also enter any text i like but I want to
gt; gt; gt; restrict to the list
gt; gt; gt;
gt; gt; gt; any ideas?
gt; gt; gt; --
gt; gt; gt; Kevin
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Toppersquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Kevin,
gt; gt; gt; gt; In my testing (XL2003) I can only enter data from the list when
gt; gt; gt; gt; I select LIST as a DV option; anything else produces an error.
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Kevinquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Hi
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I want to use data validation and have been looking at the website
gt; gt; gt; gt; gt; www.contextures.com
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; From here I have used the following formula in my data validation / source
gt; gt; gt; gt; gt; to link to a list and update automatically as new entires to my list are
gt; gt; gt; gt; gt; added / deleted
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; =OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; The problem is it allows me to enter values not in the list (there are no
gt; gt; gt; gt; gt; blank spaces when I select the list) and I would like to restrict this so
gt; gt; gt; gt; gt; that only values on the list can be restricted
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks for your help
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; Kevin