[转]Excel - How to lock cell without using macros if possible

本文转自：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