Change cell value depends on another cell value.

[gmmdinesh]

Hi Everyone,
I have a problem on my dashboard. I want to do the following in my dashboard.
If A1 cell contains Yes, the a list of drop down should available on Cell B1, If Cell A1 contains No or Nil or anything else except Yes, then Cell B1 value should N/A.
Is anyone know how to do this, please help me out.


Thanks in advance.

[jdc2000]

You could use VBA macros and cell validation to achieve what you want. I assume that you have a list of the items you want to appear in the drop down. You will need to have that list somewhere in your workbook.

[gmmdinesh]

Hi JDC,
Thanks for your Reply.
Please see the attached sample file.
Sample (2).zip

[vbfbryce]

Try something like this in the Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Dim ws1 As Worksheet
        Dim ws2 As Worksheet
        Dim rng1 As Range
        Dim rng2 As Range
        Dim lr As Integer
        
        Set ws1 = ThisWorkbook.Worksheets("Sheet1")
        
        If Target.Value = "Yes" Then
            Set ws2 = ThisWorkbook.Worksheets("Sheet2")
            lr = ws2.Range("g2").End(xlDown).Row
            Set rng1 = ws1.Range("b1")
            Set rng2 = ws2.Range("g2:g" & lr)
            
            With rng1.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Formula1:="='" & ws2.Name & "'!" & rng2.Address
            End With
        Else
            ws1.Range("b1").Value = "N/A"
        End If
End Sub

[gmmdinesh]

Hello vbfbryce,
Your code is working well...
It's working only for A1 cell..i want to do the changes on entire cells on Column A...
Please tell me what changes makes this vba to changes cells on B column depends on Column A ?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Dim ws1 As Worksheet
        Dim ws2 As Worksheet
        Dim rng1 As Range
        Dim rng2 As Range
        Dim lr As Integer
        
        Set ws1 = ThisWorkbook.Worksheets("Sheet1")
        
        If Target.Value = "Yes" Then
            Set ws2 = ThisWorkbook.Worksheets("Sheet2")
            lr = ws2.Range("g2").End(xlDown).Row
            Set rng1 = ws1.Range("b1")
            Set rng2 = ws2.Range("g2:g" & lr)
            
            With rng1.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Formula1:="='" & ws2.Name & "'!" & rng2.Address
            End With
        Else
            ws1.Range("b1").Value = "N/A"
        End If
End Sub

[gmmdinesh]

[vbfbryce]

The two changes required are immediately after the lines commented out:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        Dim ws1 As Worksheet
        Dim ws2 As Worksheet
        Dim rng1 As Range
        Dim rng2 As Range
        Dim lr As Integer
        
        Set ws1 = ThisWorkbook.Worksheets("Sheet1")
        
        If Target.Value = "Yes" Then
            Set ws2 = ThisWorkbook.Worksheets("Sheet2")
            lr = ws2.Range("g2").End(xlDown).Row
            'Set rng1 = ws1.Range("b1")
            Set rng1 = ws1.Range("b" & Target.Row)
            Set rng2 = ws2.Range("g2:g" & lr)
            
            With rng1.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Formula1:="='" & ws2.Name & "'!" & rng2.Address
            End With
        Else
            'ws1.Range("b1").Value = "N/A"
            ws1.Range("b" & Target.Row).Value = "N/A"
        End If
    End If
End Sub

[gmmdinesh]

Hello vbfbryce,
It was worked Perfectly .
Thank you so much.

[vbfbryce]

Any time!