Data validation accepts illegal entries when one spreads values

[Brillisoft]

I have a column which has Data Validation set to a column of allowed items
(Article Codes).

Validation works right when one types entries in - only valid Article Codes
are allowed in the validated column.

However, when one enters values not one by one, but by spreading an existing
cell downwards in the validated column (drag from the little + on the corner
of
the cell), values are incremented and any resulting value is welcome by Excel
in the validated cell.

E.g., values allowed in the validation list:

HP9945
HP9980
HP9994

Entering values one by one will not allow HP9946.
However, if you spread HP9945 downwards, the cells under it
will accept HP9946, HP9947, etc.

Was this the way the feature meant to be?

 

[JLatham]

By using the little + you're not copying data, you're filling data, and Excel
is seeing those numbers at the end of the entries as part of a series and
it's automatically incrementing the series. What you're seeing actually is
by design - one of those cases where Excel acts "too smart" and becomes an
inconvenience. I personally would put it in the category of bug in this
instance - but it is what it is.

 

[Duke Carey]

FWIW, XL2007 appears to handle this correctly by not incrementing above the
highest value in the validation range

 

[JLatham]

How odd, I've tried it twice in XL2007 on 2 machines. And it works as he
describes on both. Am I missing a setting somewhere? All patches/SPs
applied.

On a sheet I
set up H1:H6 with list of HP0001 through HP0006,
then selected D18 on same sheet and set data validation to list with
=$H$1:$H$6 as the list source, then
Chose HP0001 in D1,
then grabbed the lower right corner and filled down to row 14 in D and
Lo and behold I now have entries from HP0001 through HP0014 in D114 with
data validation set up in all cells showing that the source list is $H$1:$H$6
!!

If I then go to D14 and type in the same thing that's already in it
(HP0014), THEN I get the expected invalid entry message.

 

[Duke Carey]

Nope - you are right. I tested incorrectly - with a numeric validation list.

 

[JLatham]

Another one of those "I hate it when I'm right" situations. Actually should
work the other way in my estimation.