Help! Filtered Data in Listbox

[sfinney]

I want to show only the filtered data from a range in a listbox.

The filtered data will not be only one row of data but a variable number of rows.

Thanks,

S

[Domenic]

Assumptions:

1) Sheet1!A1:C5 contains the source data

2) First row contains your headers/labels

3) Column B contains the data you wish shown in your list box

Formulas:

On Sheet2, enter the following...

A1: enter a 0 (zero)

A2, copied down:

=IF((Sheet1!A2<>"")*(SUBTOTAL(3,Sheet1!A2)),LOOKUP(9.99999999999999E+307,$A$1:A1)+1,"")

B1:

=LOOKUP(9.99999999999999E+307,A:A)

C2, copied down:

If Column B contains text values...

=IF(ROW()-ROW(C$2)+1<=$B$1,INDEX(Sheet1!B:B,MATCH(ROW()-ROW(C$2)+1,A:A,0)),0)

If Column B contains numeric values...

=IF(ROW()-ROW(C$2)+1<=$B$1,INDEX(Sheet1!C:C,MATCH(ROW()-ROW(C$2)+1,A:A,0)),"")

Define the following reference...

Insert > Name > Define

Name: ListBox (or any other name you wish)

Refers to:

If Column B contains text values...

=OFFSET(Sheet2!$C$2,0,0,COUNTA(Sheet2!$C$2:$C$65536)-COUNT(Sheet2!$C$2:$C65536))

If Column B contains numeric values...

=OFFSET(Sheet2!$C$2,0,0,COUNT(Sheet2!$C$2:$C$65536))

Lastly, enter the defined name ListBox as the Input Range for your list box.

Now, when you filter your main data on Sheet1, your list box should change accordingly.

Hope this helps!

[sfinney]

I should have been more clear.

I am writing an macro in VBA. I have a range of data that is filtered. I want that filtered data to be listed in my listbox within a userform.

For Example, I have a range of names of people who work at different companies. When I click on one of the companies in my userform. I want all the names of people associated with that company to appear in the listbox.

S