UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Data Validation And Autofill, Office 2013    
 
   
bazza
post Feb 6 2018, 07:40 AM
Post#1



Posts: 339
Joined: 13-February 03



I have a column that only allows the input of certain numbers. Lets say 1,2 & 4. Now if you type the number 3 anywhere in the column it will correctly give an error due to the data validation I have set up. However if you are to type 1 in and then autofill down to 4 it allows 3 to be accepted into a cell. Data Validation shouldn't allow this but it does?

Is there a way round this behaviour?
Go to the top of the page
 
dflak
post Feb 7 2018, 12:12 PM
Post#2


Utter Access VIP
Posts: 6,145
Joined: 22-June 04
From: North Carolina


There are a couple of issues with data validation and you just described one of them. Data validation only works on data that is typed in. So if you add data by "extension" as you have, it does not catch the "error." Likewise with copy / paste. If you copy / Paste->Values only, the cell will accept whatever is pasted. If you do a regular copy / paste, not only will you replace the contents, but you will also clobber the old validation. Also adding data via VBA also bypasses validation.

The only good way around this is to use VBA. If the range of cells is definable (the cells are not scattered all over the place) and the validation is relatively simple, then this may be practical. If you are interested in trying this approach, attach a sample workboook.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
bazza
post Feb 13 2018, 08:14 AM
Post#3



Posts: 339
Joined: 13-February 03



dflak,

Thank you for your response and apologies for my late reply.

My data is very simple, in fact its exactly as I posted in the original question. All in the same column and all together (no gaps). I have no problems with using VBA if it will enable the data validation to fully do what it should. In fact perhaps one solution could be to disable autofil for those specific cells?
Go to the top of the page
 
bazza
post Feb 13 2018, 08:35 AM
Post#4



Posts: 339
Joined: 13-February 03



There may be a more efficient way to do this but the following code has worked for me:

CODE
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Column = 4 Or Selection.Column = 5 Then
Application.CellDragAndDrop = False
Else
  Application.CellDragAndDrop = True
End If
End Sub

This post has been edited by bazza: Feb 13 2018, 08:36 AM
Go to the top of the page
 
bazza
post Feb 13 2018, 09:42 AM
Post#5



Posts: 339
Joined: 13-February 03



In fact the problem with this method is that If I do want to fill down with the correct entries then I can no longer do this.

Back to the drawing board
Go to the top of the page
 
dflak
post Feb 13 2018, 10:12 AM
Post#6


Utter Access VIP
Posts: 6,145
Joined: 22-June 04
From: North Carolina


Provide a sample worksheet with a regular data validation that does what you want in place and I will try to reverse engineer from that. Also tell me to what range you would like to apply the validation.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
bazza
post Feb 14 2018, 10:44 AM
Post#7



Posts: 339
Joined: 13-February 03



I've attached a sample workbook.

Data entered into column A on sheet 1 is limited to the content that is on column A in sheet 2. Same for corresponding column B's.
Attached File(s)
Attached File  example_spreadsheet.zip ( 13.94K )Number of downloads: 2
 
Go to the top of the page
 
dflak
post Feb 14 2018, 02:01 PM
Post#8


Utter Access VIP
Posts: 6,145
Joined: 22-June 04
From: North Carolina


I'm assuming that what you want is that when a value is selected in column A, that only the value in column B is allowed. This comes more under the purview of VLOOKUP. You could protect the sheet to keep the users from messing with this formula. I did this as a demonstration. There is no password to unprotect.





Attached File(s)
Attached File  example_spreadsheet.zip ( 18.82K )Number of downloads: 1
 

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
bazza
post Feb 15 2018, 07:55 AM
Post#9



Posts: 339
Joined: 13-February 03



No that's not what I am looking for. The columns are independent of each other.

The values entered into column A on sheet 1 should be limited to the values that are in column A on sheet 2. No other values should be valid.

The values entered into column B on sheet 1 should be limited to the values that are in column B on sheet 2. No other values should be valid.

Hopefully that's a bit clearer, I may have been a bit vague originally.

Go to the top of the page
 
dflak
post Feb 15 2018, 08:29 AM
Post#10


Utter Access VIP
Posts: 6,145
Joined: 22-June 04
From: North Carolina


There are multiple duplicate values in column B ranging from 1 to 9. So you can assign any number in column B to column B.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
dflak
post Feb 15 2018, 10:40 AM
Post#11


Utter Access VIP
Posts: 6,145
Joined: 22-June 04
From: North Carolina


Try this on for size.


Attached File(s)
Attached File  example_spreadsheet.zip ( 22.32K )Number of downloads: 1
 

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
bazza
post Feb 19 2018, 09:59 AM
Post#12



Posts: 339
Joined: 13-February 03



That works like a charm.

Thank you.

PS I know there were duplicate value, I just didn't get round to removing them from the source data.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th June 2018 - 03:26 PM