Conditional cell locking/protection

Does anybody know of a way to selectively lock/unlock a cell from editing based on the value chosen in another cell? Caveat is that the Excel document cannot contain macros.

Can you provide any additional details of what is in the original cell? (The cell that causes the other one to lock). Also, what would be in the destination cell?

You can use a conditional data validation phrase to do what you would like, but I forewarn you, it is somewhat limiting.

Something simple such as "yes" or "no" would suffice. For example, cell A1 might ask the question, "will a constant growth rate be applied?" with cell B1 having the values "yes" and "no" in a drop-down.

If the value of the drop-down is "yes", then the user enters a growth rate into C1, and that rate would be used in the programmed formulas in C2:Z2 to calculate growth. If the user chooses "no", he would have to manually enter the numbers that would otherwise be automatically calculated.

I usually lock the spreadsheets after I set up a model so that only the variables/inputs can be changed. In this simple example, I would want row 2 to remain locked if A1 is "yes", and unlocked if it's "no" so that the formulas can be overwritten by actual values.

Do the numbers that will be enterred manually have a format? (ie, 1 decimal, 2 decimals, etc). Also, is there a maximum growth rate value that can be enterred manually?

I recently had a problem somewhat similar to yours, and it was somewhat difficult to find a solution. I did come up with a method that I will admit is probably not the best/most efficient method, but it did work for me.

I'm interested to know how you handled it. For the sake of argument, let's assume that the destination cells would be formatted currency to two decimal places. The growth rate in theory would go as high as 500%. The only other formatting I would have is chosen font/size/color on the destination cells.