Data Validation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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?
 
Back
Top