'Fill-in ignores Data Validation settings!

[Grisha]

The problem I have with populating previously 'data validated' cell(s)
can be explained using this simplified example:

There's a small 6-cell range A1:B3 set, by using Data/Validation, to
positive decimals only ('decimal'/'greater then'/'0') and populated as
follows:

2 3
1 1
7 7

If I now select first four cells (A1:B2) and drag them down to fill-in
to A3:B3 replacing 7 7 (equivalent to Edit/Fill/Series/Trend), I end up
there with <0 -1> (expected end of the fill-in series!) which, however,
bits my 'positive decimals only' setting. Checking 'data validation'
settings in A3:B3 shows it is still there, i.e. I can not overtype this
new input in A3:B3 cells with anything but positive decimals!

I am wondering is there a way to ensure that, in case of the scenario
described above, this 'fill-in' input is not accepted, i.e. that my
data validation setting does the job it is 'hired for'?!

Any help with this will be greatly appreciated.

Happy New Year to all Forum users from the first-time-caller.

Best regards from Toronto, Canada
Grisha.

 

[Jim Cone]

Grisha,
Doesn't appear so. From xl97 help...

"Microsoft Excel displays error messages defined for cells with the Validation
command on the Data menu only when data is typed directly into the cells.
In the following situations, the error messages don't appear:
· When the value in a cell that has data restrictions and also contains a formula
becomes invalid because the formula is recalculated.
· When a macro places data in a cell that has data restrictions.
· When a user enters data in a cell by copying or filling.
You can identify cells that contain incorrect data because of these reasons
by auditing the worksheet for incorrect data."

It is possible using programming code - VBA - to accomplish what you want
by having Excel check for changes to a particular sheet range and pop-up a
notice if a cell value in the range is out of whack.

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Grisha"wrote in message...
The problem I have with populating previously 'data validated' cell(s)
can be explained using this simplified example:

There's a small 6-cell range A1:B3 set, by using Data/Validation, to
positive decimals only ('decimal'/'greater then'/'0') and populated as
follows:
2 3
1 1
7 7
If I now select first four cells (A1:B2) and drag them down to fill-in
to A3:B3 replacing 7 7 (equivalent to Edit/Fill/Series/Trend), I end up
there with <0 -1> (expected end of the fill-in series!) which, however,
bits my 'positive decimals only' setting. Checking 'data validation'
settings in A3:B3 shows it is still there, i.e. I can not overtype this
new input in A3:B3 cells with anything but positive decimals!

I am wondering is there a way to ensure that, in case of the scenario
described above, this 'fill-in' input is not accepted, i.e. that my
data validation setting does the job it is 'hired for'?!

Any help with this will be greatly appreciated.
Happy New Year to all Forum users from the first-time-caller.
Best regards from Toronto, Canada
Grisha.