Data validation

[dakotasteve]

--I am trying to understand what I am doing wrong. I selected the entire
column C in worksheet 1 for my validation entry test. The Data validation
references the list of valid account numbers shown below, which is a range
contained in worksheet2:
10400
12200
21010
26716
29000
29037
The validation is set to reference account numbers from the above list (the
range is set up as the entire column B where this list occurs, though the
list is only 40 or so account numbers, the entire column is referenced in
case I want to add additional account numbers to the approved list at a later
date). The Data Validation tab is activated to stop and show the error alert
after an invalid entry. (I want to limit the user to selecting only those
accounts on the validation list).

My problem is when I test it, it seems to accept any account number I enter
in Cell C1 in worksheet1, without regard to the actual accounts I have set up
on my data validation list in Column B in worksheet2. There is no error
message that comes up when I enter the invalid account number, just a drop
down box that allows me to enter any number I wish. What am I doing
wrong???Any help greatly appreciated!

DakotaSteve

 

[oldchippy]

--I am trying to understand what I am doing wrong. I selected the
entire
column C in worksheet 1 for my validation entry test. The Data
validation
references the list of valid account numbers shown below, which is a
range
contained in worksheet2:
10400
12200
21010
26716
29000
29037
The validation is set to reference account numbers from the above list
(the
range is set up as the entire column B where this list occurs, though
the
list is only 40 or so account numbers, the entire column is referenced
in
case I want to add additional account numbers to the approved list at a
later
date). The Data Validation tab is activated to stop and show the error
alert
after an invalid entry. (I want to limit the user to selecting only
those
accounts on the validation list).

My problem is when I test it, it seems to accept any account number I
enter
in Cell C1 in worksheet1, without regard to the actual accounts I have
set up
on my data validation list in Column B in worksheet2. There is no
error
message that comes up when I enter the invalid account number, just a
drop
down box that allows me to enter any number I wish. What am I doing
wrong???Any help greatly appreciated!

DakotaSteve

Hi Dakotasteve,

The problem is is that you have put the whole column B as data
validation so you have 65,000+ blank cells in your validation.

Limit it to your 40 account numbers only

oldchippy

 

[dakotasteve]

Thanks OldChippy for your advice. However, is there a way I can make the
range accomdate growth in the list without having to manually update the
range every time I do it?
thx

 

[Gord Dibben]

You cannot have the input cell as part of the list.

If you want to use all of C as a list, put your DV input cell in D1 or any other
column.

OR you could have the input cell as C1 and the list as C2:C65536


Gord Dibben MS Excel MVP

 

[dakotasteve]

Old Chippy, I revised the range for the list to be C1:C40 in worksheet2, and
I still get no error message upon entering an invalid account # in worksheet1
using data validation? So the selection of the entire column C as the range
for the list may not be the source of the problem???

 

[oldchippy]

Thanks OldChippy for your advice. However, is there a way I can mak
the
range accomdate growth in the list without having to manually updat
the
range every time I do it?
thx

Take a look at this link to create a dynamic range

http://www.ozgrid.com/Excel/DynamicRanges.htm

oldchippy :

 

[Gord Dibben]

Sorry for this.

Misread the original post and thought column C was list source.

What you need is a Dynamic Range on Sheet2 so's you can allow for increased
size.

See Debra Dalgleish's site for this.

http://www.contextures.on.ca/xlNames01.html#Dynamic


Gord

You cannot have the input cell as part of the list.

If you want to use all of C as a list, put your DV input cell in D1 or any other
column.

OR you could have the input cell as C1 and the list as C2:C65536


Gord Dibben MS Excel MVP

Gord Dibben MS Excel MVP

 

[dakotasteve]

Hi There
Wow, thanks for the tip on the dynamic range. I will have to try that. But
for now, for simplicity's sake, I tested this by reducing the list range in
column B to just 40 rows, b2 through b39 in worksheet 2 (this range is named
TB_Account). In the validation entry worksheet1 which selects all of column
C in worksheet 1 for data validation with a reference to the list contained
in the name range "TB_Account" in worksheet2, when I test I still get no
error message when I enter a "non-list" account number into the cell which
should require validation. Very puzzling! Anyone know why?

 

[Gord Dibben]

I cannot replicate your problem unless I uncheck the "Show error alert etc."

Gord

 

[dakotasteve]

Thanks Gordon and Chippy. My "show error alert" is checked and I still get
no error message when I enter in an account code that is not from the list,
so unless I get more advice I will have to assume I have an unexplained
anomaly in my Excel program.
Steve

 

[Larry]

Hey DS,
I read the variouos posts here, I am a bit of a rookie too. I have some code
that I got from the contextures sample code site. See if this helps you get
what you are after.
Open a work book, have two sheets, one called "Lists" (your data) and one
called "NameList" (your input sheet)
On the Lists sheet, create your range of data in column A , then highlight
all of range you want to use, I used 500 cells. At the top of the window
across from fx there is a name window, it shows what cell you are in. Type
the name NamedList in the window and hit enter, this will name your range of
data.
click o.k. and you should be on your way. You may have to experiment a
little with it. Good luck with it. larry
Now go up to the Insert button and click Insert, then name, then Define.
There you will see your newly created nameList, select it and in the "refers
to" window paste the following:
=OFFSET(Lists!$A$1,0,0,COUNTA(Lists!$A:$A),1) click O.K.

Now open your NameList sheet ( Actually, you can rename it to what ever you
want). Highlight all the cells in a range in what ever column you want to use
(again, I used 500 cells) then go up to Data, then select Validation
and a window will open, in the validation criteria drop down select List. At
the botttom a new window will open for the source, select the settings tab;
in this type =NameList (the same as the defined list name you created). now
select Input message tab, you can create a custom message for your users
here. check the "Show input message when cell is selcted if you want to use
this feature. now go to the Error Alert tab Check the box at the top th Show
an eror after invalid data is entered.
You can also choose to enter a message to the user here to direct them to
enter only the appropriate data.

 

[dakotasteve]

Thanks Larry
I am using Excel 2002, I think what I have discovered is that the named list
range has to be on the same worksheet where the validation is occuring in
order for the validation function to work. I don't know if Excel 2003 fixed
this or not!
thx all

 

[Gord Dibben]

Steve

If you name the list range on the other sheet or workbook(if open) then it can
be used in DV.

In the source dialog you would enter =MyList where MyList is a named range.

This goes all the way back to Excel 97 and has not changed so no fix required.


Gord Dibben MS Excel MVP

 

[dakotasteve]

Hi Gordon
YOur comments regarding the use of a named range on another worksheet are
exactly what I assumed from reading my Excel texts, however, in my example,
when I name the range in another worksheet and reference it, the validation
process ignores it, but when I refer to "MyList" (the named range) and place
it on the same worksheet in a column to the right of where my validation
occurs, it works fine. Very strange.
Steve