Using a IF function in a validation list cell

[Hackipotimus]

I want to have an item on a validation cell automatically selected when an
item in another cell's list is selected. I kind of feel like it would be
something like:
=IF(COUNTIF(D9,"*Base*"),24, UNKNOWN COMMAND ). This would look for the word
"Base" in the cell D9 and if true would select 24 from the list, and if false
I want it to defer to the user's selection.

Not even sure this can be done.

 

[Jacob Skaria]

-You can name the validation list range to say 'myRange' and enter 24 to
another cell and name it 'myRange1'. Use the below formula for validation
list criteria

=IF(COUNTIF(D9,"*Base*"),MYRANGE1,myrange)

--OR using VBA..

Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the
right blank portion. Get back to to workbook and try out.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$9" Then
Application.EnableEvents = False
If InStr(1, Target, "base", vbTextCompare) Then Range("E9") = 24
Application.EnableEvents = True
End If
End Sub

If this post helps click Yes