Dropdown Value List with Variable Items based on selected value of another field

[NWPhotoExplorer]

Hello all,

I am trying to figure out how to make a dropdown list that will display only certain items based on the selection of a field. Right now, I have a "name" field in column G, a "type" field in column H. I want the dropdown in column I.

Basically, how I would like it to work is that someone types in the name in column G. This is a 10 digit name. First 5 are letters and the last 5 are numbers. Then they select the type, either 1x1 or 1x2. Column I would then show either 1 or 2 items based on if it was a 1x1 or 1x2.

If it was a 1x1, it would take the left 9 and add a "1" in the last place. If it was a 1x2, it would take the left 9 and add either a "1" or a "3" to the last position.

I cannot figure out how to write something that would accomplish this for me. Here is an example of the output I am trying to achieve.

FLW REQUEST.xlsx
	G	H	I
1	Node Housing Name	1x1 or 1x2	RPD NAME
2	CAADD99900	1x1	CAADD99901
3	CAADD99900	1x2	CAADD99901
4	CAADD99900	1x2	CAADD99903
Sheet1

I hope I have explained this well enough.

Thanks in advance!

 

[richh]

Not sure why you want a drop down, but you can get the (somewhat) expected results with a nested IF

I say somewhat because I don't what what condition your data would need to meet in order to differentiate when to put a "1" or "3" at the end of the string if you select 1x2.


=IF(H2 = "1x1", LEFT(G2, 9) & "1", IF(H2 ="1x2", LEFT(G2,9) & "3", "Error")

 

[NWPhotoExplorer]

Not sure why you want a drop down, but you can get the (somewhat) expected results with a nested IF

I say somewhat because I don't what what condition your data would need to meet in order to differentiate when to put a "1" or "3" at the end of the string if you select 1x2.


=IF(H2 = "1x1", LEFT(G2, 9) & "1", IF(H2 ="1x2", LEFT(G2,9) & "3", "Error")

I can't think of a better way to achieve this, so that is why I posted the question hoping someone else could come up with something. I need something, like a drop-down, because when the node is a 1x2, it will have the "1" RPD name, but it could have the second piece of equipment in there which I would need another line to show the "3" name as then i would have to show the other fields with the "C" and "D" names.

 

[richh]

Gotcha. Here's the VBA Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 8 Then
        Range("I" & Target.Row).Validation.Delete
        
        If Target.Value = "1x1" Then
            Range("I" & Target.Row).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Formula1:=VBA.Left(Target.Offset(0, -1), 9) & "1"
        ElseIf Target.Value = "1x2" Then
            Range("I" & Target.Row).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Formula1:=VBA.Left(Target.Offset(0, -1), 9) & "1" & "," & _
            VBA.Left(Target.Offset(0, -1), 9) & "3"
        Else
            Range("I" & Target.Row).Validation.Delete
        End If
    End If
End Sub