I am using data validation on one of my columns to only allow the user to enter data from a list. The list sources a dynamic range but the data is usually 1st, 2nd, 3rd. The problem I have is if the user clicks in the bottom right corner of a cell in this column and drags down to copy the data to multiple cells, rather than copying the same data excel automatically goes up (4th, 5th, 6th....etc), and it doesn't get prevented by the data validation. How can I stop this from happening?
hi check whether cell references are locked under data validation> settings> source shoud be like this =$H$1:$H$3 not like this =H1:H3
☚ Click ★ just below left if it helps, Boo?ath?
The range is defined by a named dynamic range, as the user can add to it using a form. This is the formula for the range:
=OFFSET(UnitList!$A$1,0,0,COUNTA(UnitList!$A:$A),1)
I dont think this makes much difference though. I tested a column with a manually defined list of 1st, 2nd, 3rd, and it still allows the user to click in the corner and drag down to input 4th, 5th, 6th etc which isn't in the list. See column A on attached workbook.
Anyone have any thoughts on this issue?
That is 1 of the short-comings of DV, copy/paste will get past the DD rules
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I already have a macro which allows the user to easily duplicate data down to cells below, but users that already have some excel knowledge are used to clicking and dragging down and it really messes things up in the workbook. Is there not a way to disable this, as I dont need this functionality (since I have added macros to basically do the same thing)?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks