本文转自：http://stackoverflow.com/questions/11953214/excel-how-to-lock-cell-without-using-macros-if-possible
You can prevent a user entering new data using only Data Validation
EDIT: use formula in list
Thanks to Head of Catering's comment, it is worth remembering that you can use formulas with Data Validation / List.
See his answer to another thread to see this in action: http://stackoverflow.com/a/11902463/138938
Original post
Walkthrough for Excel 2007:
- Ribbon > Data > Data Validation
- Authorize : Personnalized (or similar, my Excel is not in English sorry)
- Enter this formula in the field:
=IF(A1="",FALSE,TRUE)
Thus, you cannot enter a value in the cell if A1 is still empty
Got it to work, but you can only actually set the value when it works out to a dropdown
I used Name Manager to set the name test to the value =IF(Sheet1!$A$1=1,"",Sheet1!$E$1:$E$5)
this means that if A1 is 1, I get nothing, and if A1 is anything else I get E1:E5
Then I set the data validation to List, with source to =test you can only set the value when test returns a list
When I do this, when A1 is 1, I get an empty list in my validation dropdown, and I can't change the value. If A1 is not 1, I get my list of E1:E5, and I can change the value