All,
I have a cell with data validation to restrict entry to decimal value greater than some value. However, if I right click that cell and select "Pick From Drop-down list..", it allows any value from the drop-down list (which are are all values above that cell).
I have attached a sample worksheet
Any help will be greatly appreciated.
Hi sjmxls,
You need to decide which rule to use. If you don't want the >2 rule then click on the Data tab and remove the data validation rule. If you only want stuff above to be entered then use those cells as the data validation.
Excel is pretty versatile. It allows things in conflict to be accessed.
Hope this helps
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Interesting - seems to be a bug that the drop-down list of entries above a cell can be used to get round the data validation rule.
A quick solution is to have a blank cell above the DV-cell (completely blank, no data or formula). The drop-down list will then also be blank. You can hide the row with that blank cell and it will still keep the drip-down blank.
There may be a more sophisticated method of fixing this, of course, but I couldn't think of one right now.
Regards,
Aardigspook
I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
You don't need to give me rep if I helped, but a thank-you is nice.
I tried adding blank cell as you suggested and it worked great. However, it did not work on the sheet that I was working on. I copied and pasted values to the new sheet and it has the same issue. See attached sheet. Right click on blue cell with value 3 (cell A15)Originally Posted by Aardigspook
Insert a new row above row 15 and the problem goes away again. I guess (but don't know) that it was the copy-and-paste of values which caused it not to work (copy and paste values of a cell which is blank because of a formula can result in a cell which looks blank but Excel treats as not blank).
I have no idea why the 'Pick from Drop-down' / DV combination should do this and it's annoying to have to use such a work-around, but it's the best I can come up with. I'll reach out to the experts on the forum - someone with more knowledge than me might have seen this before and/or have a better solution.
Adding a new row above row 15 did not work. But I noticed there was a formula below in cell C25. So clearing the cell C25 or adding another row below C24 worked. Strange!
Thank your for suggesting the workable solution. Sure seems like a bug to me. Hopefully, it will be fixed.
Copy paste will also ignore DV
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
Yeah. The copy paste was only done to show in the example worksheet that I attached. Had the same issue with my original worksheet that I was working on where I noticed the issue.
Thanks for the input.
That is not what I meant.
You can also copy a random cell en then paste this in the cell with DV
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks