Yes No Function with Data Validation

learningexcel22 · Jul 28th 2014

I'm working on an excel sheet and I have added Yes/No data validation drop downs. I would like to add a formula that will control the outcome of other cells when one cell is selected. For example, if I select Yes for working, I would like the other cells to revert to No. If I selected Yes for Scheduled off, all other cells should be No. The same for all other columns

[TABLE="class: grid, width: 500"]

[tr]

[td]

Working

[/td]

[td]

Scheduled off

[/td]

[td]

Vacation

[/td]

[td]

Called Out

[/td]

[td]

Training

[/td]

[/tr]

[tr]

[td]

Yes

[/td]

[td]

Yes

[/td]

[td]

Yes

[/td]

[td]

Yes

[/td]

[td]

Yes

[/td]

[/tr]

[/TABLE]

learningexcel22 · Jul 28th 2014

Re: Yes No Function with Data Validation

If there is something similar posted elsewhere in this forum, please let me know. I've tried looking, but did come across anything. I believe that this is relatively simple (yet I cannot figure it out).

scott_n_phnx · Jul 28th 2014

Re: Yes No Function with Data Validation

This may be a bigger thing than you wanted, but you can try this vba code. Place it into the worksheet module for the worksheet where you are working.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
    If Target.Value = "Yes" Then
        Target.Offset(0, 1).Value = "No"
        Target.Offset(0, 2).Value = "No"
        Target.Offset(0, 3).Value = "No"
        Target.Offset(0, 4).Value = "No"
    End If
End If


If Target.Column = 2 Then
    If Target.Value = "Yes" Then
        Target.Offset(0, 1).Value = "No"
        Target.Offset(0, 2).Value = "No"
        Target.Offset(0, 3).Value = "No"
        Target.Offset(0, -1).Value = "No"
    End If
End If


If Target.Column = 3 Then
    If Target.Value = "Yes" Then
        Target.Offset(0, 1).Value = "No"
        Target.Offset(0, 2).Value = "No"
        Target.Offset(0, -1).Value = "No"
        Target.Offset(0, -2).Value = "No"
    End If
End If


If Target.Column = 4 Then
    If Target.Value = "Yes" Then
        Target.Offset(0, 1).Value = "No"
        Target.Offset(0, -1).Value = "No"
        Target.Offset(0, -2).Value = "No"
        Target.Offset(0, -3).Value = "No"
    End If
End If


If Target.Column = 5 Then
    If Target.Value = "Yes" Then
        Target.Offset(0, -1).Value = "No"
        Target.Offset(0, -2).Value = "No"
        Target.Offset(0, -3).Value = "No"
        Target.Offset(0, -4).Value = "No"
    End If
End If


End Sub

Display More

learningexcel22 · Jul 28th 2014

Re: Yes No Function with Data Validation

Yes, it's a little too complicated and does not seem to be working with my worksheet.

scott_n_phnx · Jul 29th 2014

Re: Yes No Function with Data Validation

Unfortunately, for what you are looking to do, there really isn't another option, other than vba. This one will watch the row and then change the values as needed. Because you are using Data Validation drop downs, a formula will not work.

As for the code that I provided not working, you will need to ensure that it is located within the worksheet module for the worksheet where your Yes/No values are located.

-ALT+F11
-Expand VBAProject(Your Workbook.xlsm)
-Double-Click on worksheet module
-ex. Sheet1(Sheet1)
-If the code is a worksheet event (i.e. worksheet_change, worksheet_beforedoubleclick) the code MUST go into the worksheet module for the worksheet that is being watched
-Paste in code

Worksheet events CANNOT be run manually

-To run
-Within module, click the Run Macro button at top of VB Editor
-From worksheet, go to Developer tab and select Macros - select your macro and Run

learningexcel22 · Jul 29th 2014

Re: Yes No Function with Data Validation

Then it is definitely me. Would you mind if I sent you the file.......in a private message?
Thanks,
Diana

scott_n_phnx · Jul 29th 2014

Re: Yes No Function with Data Validation

Sure. I will take a look at it.

Yes No Function with Data Validation

Participate now!

Share