Lock particular cells in a worksheet

[PaulG19]

Hi,

I am using excel for a survey which I will send out via email to respondents and want to

- Prevent them from adding rows and or columns
- Only be able to enter data in selected cells
- Also write some rules/conditions that will appear with a prompt (i.e I ask respondents how they split their time across activities as a % and will need a range of cells to sum to 100%)

Thank you in advance

Regards

Paul

 

[kweaver]

You'll first have to select the cells where user entry is permitted. Then, uncheck the "Locked" option from Format Cells.
Then, the sheet AND the workbook will have to be protected using a password.

In the cells where the % of time is to be entered, use Data Validation to permit only values <=100% and a total not to exceed 100%.
After selecting the cells where this should apply (e.g., in my example, I selected C4 through F4), click on Data Validation and select Custom and in the formula box, enter:

=AND(C4<=100%,SUM($C$4:$F$4)<=100%)

(here I restricting entry of 100% or less in C4 through and including F4 AND also not allowing the sum of that range to be more than 100%.

Format those cells to be % so the user doesn't have to type the percent symbol each time.

 

[PaulG19]

Thank you - That's very helpful