98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Data validation
Data validation
Resolved · Low Priority · Version 2003
Wed 11 Mar 2009, 16:26Delegate Aurelie said...
Aurelie has attended:
Excel Advanced course
Data validation
How do we lock the list of validation so no one can change it if they know how to set it up?
Thu 12 Mar 2009, 10:10Trainer Amanda said...
RE: data validation
Hello Aurelie
Thank you for your question and welcome to the forum.
This is a trickier problem than I initally thought. The problem is that if you create a dropdown list using data validation and then you apply protection to the spreadsheet, a person who is using the spreadsheet is unable to then select items from the dropdown list.
It seems that the only way to get around stopping people from pasting into cells with data validation whilst still allowing them to use a dropdown list, is to write VBA code to prevent people from pasting into cells that have data validation applied :(
Kind regards
Amanda
Fri 13 Mar 2009, 11:31Delegate Aurelie said...
RE: data validation
Hi Amanda,
Thank you for the below. I was sure that we could protect only the source though?
I had an other question, initially I had set up my source on the same sheet but it is not suitable anymore as the source is affected when I sort my rows/columns etc.
I wanted to create the source then on a different sheet and link it to the first one but it doesn't let me! How can I select the source from an other sheet if I want to select a table and not write the wole date in the source cell in the data validation window?
Hope my question makes sense.
Thank you
Aurelie
Fri 13 Mar 2009, 15:35Trainer Amanda said...
RE: data validation
Hello Aurelie
Thank you for your question.
Yes, you can set protection on the source of your data (Tools - Protection - Protect Sheet).
To use a source from another sheet, you will need to give a name to the source cells (e.g. give the name list to the cells), then when you set up the validation in the Source box type in =list.
If you're not sure how to name a range of cells, let me know or look up in Excel Help :)
Kind regards
Amanda
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Checking if a calculation adheres to Order of PrecedenceWhen writing formulas you must make sure that results will be calculated as you intended. |
