Drop Down Selective list

[LadyHarper]

I have a list in Column A, This is my drop down options. However I only want it to select the options in Column A to display in the drop down, If the corresponding row in in Column C is greater then 0.

 

[Anfinsen]

I think you need add a helper column, something like this in the helper column,

=IF(C1>0,A1,"")

then your dropdown pulls from the helper column. Does this make sense?

 

[Jeffrey Mahoney]

I'm not sure this is what you want. The Main list is you actual list of items. The OFF/ON List shows when the value in column C for the current row is greater than Zero. The macro refreshes the value in the OFF/ON List each time the cursor moves.

Book1
	A	B	C	D	E	F	G	H	I
2	One	Two	Three				Main List		OFF/ON LIST
3			15				1		1
4			0				2		2
5			16				3		3
6							4		4
7
Sheet1

Cell Formulas
Range	Formula
I3:I6	I3	=IF(INDEX(A:ZT,CELL("Row"),3)>0,G3:G6,"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A3:A7	List	=$I$3#

This code needs to be put in the Sheet module for the sheet you have the Pulldown lists in column A

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Range("I3#").Calculate
End Sub

 

[LadyHarper]

Yes Makes sense, and works. Are you able in your If statement to add, if N/A then leave blank?

 

[Jeffrey Mahoney]

Replace the formula in cell I3 with this: =LET(C,IFERROR(INDEX(A:ZZ,CELL("Row"),3),0),IF(C>0,G3:G6,""))

It will work if it finds any error in column C

 

[Bosquedeguate]

Here is a quick example to conditionally populate a ListBox or ComboBox

When the ComboBox gets focus the list is conditionally updated via the following code. Source data for the list box is in A7:C13

Private Sub ComboBox1_GotFocus()
  Dim rng As Range
  Dim lr As Long
  Dim cb As Collection
  Dim r As Long
  lr = Range("$A7:$A13").Rows.Count
  
  With ComboBox1
    Do While .ListCount > 0
      .RemoveItem (.ListCount - 1)
    Loop
    
    Set rng = Range("$A$7:$C$13")
    For r = 1 To lr
      If rng(r, 3) > 0 Then: ComboBox1.AddItem rng(r, 1)
    Next r
  End With
End Sub

Book2 (Autosaved).xlsm
	A	B	C
5
6
7	a		1
8	b		0
9	c		0
10	d		1
11	e		1
12	f		0
13	g		1
Sheet2

 

[LadyHarper]

I think you need add a helper column, something like this in the helper column,

=IF(C1>0,A1,"")

then your dropdown pulls from the helper column. Does this make sense?

So far this makes the most scenes to use. As I have a dropdown list from data validate, however for the ones, that have 0 I get an N/A. Is there a formula to remove the N/A?