Excel: Avoid Overwriting Dropdown cell options & Cell Validation List: enforce capitalization

Question

Dear community,

I would like to send a file with drop down options to other people. Even though I use "Edit Range" to protect the rest of the sheet which should not be edited, I still have the issue that some respondents copy cells without data validation checks on the cells where I had the drop down before. Is there a way to prevent this from happening?

Additionally, some respondents use only capital letters which is accepted by the data validation. Can this be prevented too?

Thank you!

Answer 1

Hello exceluser_123

I am V. Arya, Independent Advisor, to work with you on this issue. Functionality to stop pasting into Data validation is a long pending demand from Excel users. There is already a feature request logged at Uservoice where you should also go and endorse - https://excel.uservoice.com/forums/304921-excel...

However, there is an interesting VBA solution suggested at https://superuser.com/questions/870926/restrict....

The second part can be accomplished by following kind of data validation formula

=EXACT(A1,LOWER(A1))