Dynamic drop down list (Preferably without VBA)

[excel_aspirant]

I am struggling with a problem. My spreadsheet has 2 columns, activities and % time spent on each activity. % of time is not entered for every activity i.e. there are some blank rows for % of time.

Based on whether an activity has an associated % of time, I want to create a data validation list to allow users enter overtime activities. In short, to highlight overtime activities, present only those activities to an user that have % time associated with them.

The overtime is another section of the same worksheet. Any help you can provide will be greatly appreciated.

 

[Simon Lloyd]

You would have to use VBA if you wanted to have the validation dropdown only available on blank cells.

 

[excel_aspirant]

I want the dropdown list to contain only the non-blank elements from the activity column.

 

[Simon Lloyd]

Ok, create a userform with one combobox (it can be just the size of the combobox ), add this code to it's code module

Private Sub ComboBox1_Change()
ActiveCell.Value = Me.ComboBox1.Value
Unload Me
End Sub

Private Sub UserForm_Initialize()
  'Populate Color combo box.
  Dim ListVal As Range
  Dim ws As Worksheet
  Set ws = Worksheets("Sheet1")
  For Each ListVal In ws.Range("MyCol")
  If ListVal = "" Then
  Else
    Me.ComboBox1.AddItem ListVal.Value
End If
  Next ListVal
End Sub

Change the sheet name in that code to your sheet name, then go to the menubar INSERT>NAME>DEFINE and enter this in the formula bar =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1) (change the sheet name and column letter for that of your column you are working on, in the name bar at the top enter MyCol and hit enter, on your worksheet that you are working with enter this in it's code module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then UserForm1.Show
End Sub

change the 1 to whatever the column number is that you are working on, and there you have it