Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
'Fill-in ignores Data Validation settings!
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. -- Grisha ------------------------------------------------------------------------ Grisha's Profile: http://www.excelforum.com/member.php...o&userid=30200 View this thread: http://www.excelforum.com/showthread...hreadid=499086 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
'Fill-in ignores Data Validation settings!
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation | Excel Discussion (Misc queries) | |||
Loading Column Data with blank Rows into Data Validation Box | Excel Worksheet Functions | |||
Case Sensitivity problem with data validation | Excel Worksheet Functions | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Identifying the Active Fill Color | Excel Discussion (Misc queries) |