Thread: If cell a1=x, then cell b1=y

I am working on a spreadsheet that requires cell I4 to be a drop down selection. What I want is to have cell I6 display different things depending on the selection made in cell I4.

Cell I4 menus :
Please select (I want I6 to be blank)
AA (I want I6 to display title "AA Name:") then J6 to display a drop down list of names IF possible, if not they can type it in)
EWR (I want I6 to display title "Date submitted:")
BP (I want I6 to display title "BP Approved by rep on:")

Can someone help me with this?

Try:

=LOOKUP(I4,{"AA","BP","EWR","Please Select"},{"AA Name:","BP Approved by rep on:","Date Submitted:",""})

Also, if you want a list for when I4 is AA... then create the list of names on the side somewhere to include, name it via Insert|Name|Define and call it something like List. Then apply Data Validation to I6 and select List and insert formula in the Source Field: =IF(I4="AA",List)

Try this workbook

Make three Named lists.

In this example := Code, CodeLookup, and Names
All are dynamic in this sample, add to or delete from them if required.
Just remember := Don't leave blanks within the lists

In the Sheet Module Change Event

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$I$4" Then
        With Range("J6")
            .Validation.Delete
            .Value = ""
        End With
        If Target = "AA" Then
            With Range("J6").Validation
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                     Operator:=xlBetween, Formula1:="=names"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
        End If
    End If
End Sub

Fill the warnings/titles to suit your needs

Hope this helps.

[EDIT]
If you go NVBCs' way, remember to uncheck the error alerts otherwise you will not be able to put anything in J6 that is not on the list.

OK thanks Marcol for help... and sorry for my mistake