Conditional List

balusm

New Member
Joined
Apr 5, 2014
Messages
17
Hi

In a cell I need a conditional formula(?) based on the value of another cell.

If another cell = "yes" then I want it to require user to choose one of the values from a list
If another cell = "no" then I need just to enter text 'N/A' and no offer any list

How can I do this?

It's probably not too complex.

Many thanks
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
You can use dependent data validation for this.

List the options for when the cell is yes and name that range "yes"
put N/A in a cell and name the range "no"

Select the cell you want this user change and in data validation choose list and as the source use, where E1 is the anther cell that is yes or no.
Code:
=INDIRECT(E1)

When the other cell is Yes the list will be you options and when it is no the only option will be N/A
 
Upvote 0
Hi Scott

I can get the conditional lists working but what I'm trying to achieve is that if the chosen value is 'No' to automatically populate the cell with 'N/A', rather than put a dropdown there with only 'N/A' that still needs to be chosen.

Is that possible?
 
Upvote 0
How is the Yes or No put into the cell? By formula result or entered by the user?
 
Upvote 0
Right click on the tab and select view code. Past this and change cell references as need. Replace opt1,opt2,opt3 with your list

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E1")) Is Nothing Then
    If UCase(Range("E1")) = "NO" Then
        Range("A1") = "N/A"
    Else
        With Range("A1")
            With .Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="opt1,opt2,opt3"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
            End With
    End With
    End If
End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top