array formula

  • Thread starter Thread starter jmorgs
  • Start date Start date
J

jmorgs

I want to make an array formula that find only NON blank cells. Thi
formula will be in a dropdown. so I do not want it to sum the rows
only find them. I have a column of information for my dropdown, and
want it to only display the NON blank cells in my dropdown. Thanks!
:
 
Suppose your range of blank and non-blank cells is
A1:A20. In row 1 of another column (say col. B), insert:

=INDEX($A$1:$A$20,SMALL(IF($A$1:$A$20<>"",ROW
($A$1:$A$20)),ROW()))

and press ctrl/shift/enter. Fill down until you start
seeing #NUM!.

Now use this as the source for your list in Validation:

=OFFSET(B1,,,COUNTA(A:A))

HTH
Jason
Atlanta, GA
 
Is the CountaA(A:A) part suppose to be the hieght? The formula will not
work and tells me that I have an error, and I do not completely
understand how this formula is suppose to work. This is the formula
that I have in my cell now, this formula moves the source rows over
depending on what you choose in the previous dropdown:
=OFFSET($I$4:$I$11,0,MATCH($C$15,$I$6:$I$32,0))
Thanks!
 
A little variation to the last code I gave you....this will take out th
blank rows for you and only add unique items....

Again..this is a scenerio where the user will select a state and the
the wholesalers are populated into the combobox....



Sub FilterWholesalers()
Dim Row As Integer, i As Long, j As Long
Dim bUnique As Boolean
Set testWholesaler = ThisWorkbook.Sheets("lookup on brewery no
refrig")
TopicCount
Application.WorksheetFunction.CountA(testWholesaler.Range("A:A"))

cbWholesaler.Clear

For Row = 1 To TopicCount
If Sheet4.Cells(Row, 4) = cbState.Value Then
If cbWholesaler.ListCount = 0 Then
cbWholesaler.AddItem testWholesaler.Cells(Row, 3).Value
Else
bUnique = True
For j = 0 To cbWholesaler.ListCount - 1
If cbWholesaler.list(j) = testWholesaler.Cells(Row
3).Value Then
bUnique = False
Exit For
End If
Next
If bUnique Then
cbWholesaler.AddItem testWholesaler.Cells(Row, 3).Value
End If
End If
End If
Next Row

cbWholesaler.ListIndex = 0

CurrentTopic = 1
End Su
 
Back
Top