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 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 quot;RegionStartquot;)
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 Govierquot;Kevinquot; gt; wrote in message
news
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 /
gt; source
gt; to link to a list and update automatically as new entires to my list
gt; are
gt; added / deleted
gt;
gt; =OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)
gt;
gt; The problem is I can still enter values not in the list (there are no
gt; blank spaces when I select the list) and I would like to restrict this
gt; so
gt; that only values on the list can be entered
gt;
gt; Through help on this forum I have figured the issue is most likely
gt; related
gt; to the fact that above formula refers to a named range (eg
gt; quot;RegionStartquot;)
gt; rather than specific cells (eg $A$1). Any idea why this is or other
gt; options
gt; for me
gt;
gt; thanks
gt; --
gt; 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 quot;Warningquot;
to quot;Stopquot;.

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?
--
Kevinquot;Roger Govierquot; wrote:

gt; Hi Kevin
gt;
gt; I would think the most likely cause is that the Error Alert box is not
gt; checked on the third tab of the Data Validation setup dialogue.
gt;
gt; --
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt; quot;Kevinquot; gt; wrote in message
gt; news
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 /
gt; gt; source
gt; gt; to link to a list and update automatically as new entires to my list
gt; gt; are
gt; gt; added / deleted
gt; gt;
gt; gt; =OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)
gt; gt;
gt; gt; The problem is I can still 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
gt; gt; so
gt; gt; that only values on the list can be entered
gt; gt;
gt; gt; Through help on this forum I have figured the issue is most likely
gt; gt; related
gt; gt; to the fact that above formula refers to a named range (eg
gt; gt; quot;RegionStartquot;)
gt; gt; rather than specific cells (eg $A$1). Any idea why this is or other
gt; gt; options
gt; gt; for me
gt; gt;
gt; gt; thanks
gt; gt; --
gt; gt; Kevin
gt;
gt;
gt;