Locking Columns on a Questionnaire Spreadsheet

[jgleon22]

Hi:

I am trying to develop a questionnaire spreadsheet using a ranking system. I have different questions (based on certain criterias) and four columns for each question: NEVER | SOMETIMES | MOST OF THE TIME | ALWAYS.

I want to see if there is a way that I can lock the other three cells when the user has chosen a particular column by typing an X. BY doing so, I want to ensure that the user will not choose two options (or columns) instead of only one, because if he/she does it will mess the other calculations that are not shown within the table. Only the input and output are shown.

I need this urgently! Please reply. Thanks in advance for your help!

[Andy Pope]

You could use a custom Data validation to restrict input to a single value in C3:F3

=COUNTA($C$3:$F$3)=1

[jgleon22]

Thank you soooo much Andy! Youre a lifesaver!

[jgleon22]

Andy,

So I customized the data validation for my questionnaire, and right now I am trying to add another restriction to the same cells. I want to make sure that the user is obligated to use the letter x to choose a particular cell. I am trying to edit the formula that you gave me =counta($H7:&K7)=1 like this:

=and(counta($H7:&K7)=1,(($H7:&K7)="x") but its not working. DO you know what I am doing wrong?

By adding this portion to the original formula, if the user enters any text, the error message pops up.

Thanks in advance!

[Andy Pope]

Select all range H7:K7 and try


=and(counta($H7:$K7)=1,upper(H7)="X")

[jgleon22]

That didnt work. Im not sure. I also tried this: =and(counta(H7:K7)=1,(H7:K7)<>"x") but I dont know why but it restricts the user to type in an x in the column, whereas it allows all other upper and lowercase letters to be typed in. I want it to be the other way. I want to restrict the user from choosing other characters that are not the X. Let me know if you think of anything else. Thanks!

[Andy Pope]

Not sure what you are doing but his works.

[jgleon22]

Yeah, I was missing an = sign. THank you so much. Two last questions, 1) what the logistics behind that equation (I understand the first part [counta] and the AND function, but why use UPPER and only one cell? I thought UPPER was to change text strings to uppercase.If you have time, please let me know about this. 2) how can I let other people know that my problem has been solved, and do I have to take a survey or something to give you credit? Let me know

Thanks!

[Andy Pope]

The UPPER part means you can enter either upper or lower case in the cell but the formula will only need to check for upper case X as the cells contents are first converted to upper case.

The absolute and relative references in the formula in conjunction with assigning the formula with multiple cells selected means that the $H$7:$K$7 reference remains static for all cells. Where as the H7 reference will automatic update to I7, J7 and K7 for each of the cells selected.
You need to use a single cell as H7:K7="X" will return #Value.

You can mark you thread as solved and if so inclined add to a users reputation.
http://www.excelforum.com/faq.php