how to only pick and show one cell

[Sergio]

I have a questionnaire and 3 possible answers. How can I make sure only one answer is selected Column B, C, and D they are a yes, no, maybe answer (columns).
I will add a value to each answer so at the end I can use the formula provided by an user in my last post
Thanks
Alex

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[awoohaw]

Sergio, why are you having your team select one of three columns? With a data validation you can have a drop down limited to the three options.

 

[Sergio]

the problem is that i might need to change the format of the file and it will be more difficult if i start using drop menu. The questionnaire have sections and what i am trying to do is to create a streetlight code.
For Example if all of the questions are Yes that means they are all green, if one of the questions is a NO that mean is red and of course one question is Maybe that mean the section will be yellow.
Some people are selecting two options and it make it difficult to quantify

 

[awoohaw]

You could use additional conditional formatting that will alert the user that more than one cell is populated.
Or
You can use VBA to delete the value in one cell if one of the others is populated. I am not an expert on VBA so I cannot contribute that.

 

[mumps]

Start by unlocking all the cells in your sheet. Next copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change the password (in red) to suit your needs. Close the code window to return to your sheet. Enter an "x" in any cell in B:D and press the ENTER key. The other adjacent cells will be locked.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("B:D")) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect "MyPassword"
    Select Case Target.Column
        Case 2
            Target.Offset(, 1).Resize(, 2).Locked = True
        Case 3
            Target.Offset(, 1).Locked = True
            Target.Offset(, -1).Locked = True
        Case 4
            Target.Offset(, -2).Resize(, 2).Locked = True
    End Select
    With ActiveSheet
        .Protect "MyPassword"
        .EnableSelection = xlUnlockedCells
    End With
End Sub

 

[Sergio]

Thank you but the macro doesn't let me change my mind if I picked yes and then I want to change to No.
I am just looking to don't have the option to pick two values.... the Macro blocks the rest of the excel sheet and i do still have some stuff in different columns
Thanks
Sergio

 

[mumps]

You have to start by unlocking all the sheets cells as I mentioned in my post if you want access to the other columns.