ComboBox populated with data from AutoFilter
 

Hi

I am trying to generate a pivot table having the pivot fields passed
as parameters from five drop down lists.

Is there any posibility to populate the five drop down lists with the
data from Auto filters, instead of linking to a range.

Example:
first drop down list=Region(Asia Pacific, Americas, Europe)
second drop down list=Countries(Korea,China,Canada,Japan,France etc)
and so on ...

When I select Europe from the first drop down list, at this stage, the
second list is populated with all the countries , not only the
countries from Europe.(as an example, if I select Europe from the
first list, and Japan from the second list, there is no data generated
by the pivot table)

Is there any posibility to filter somehow the drop down lists, eg when
I select Europe from the first list, the second one should be
populated only with countries from Europe and so on...

Could anyone help me with this?

Thank you in advance,
Cristian

ComboBox populated with data from AutoFilter
 

If you use the dropdown lists from the autofilter, this is done
automatically. If not, you can get the visible rows with

Dim rng as Range, rng1 as Range, cell as Range
With ActiveSheet.autofilter.Range.Columns(1)
set rng = .offset(1,0).resize(.rows.count-1)
End With
On Error Resume Next
set rng1 = rng.SpecialCells(xlVisible)
On Error goto 0

If not rng1 is nothing then
for each cell in rng1
worksheets("Sheet1").Combobox1.Additem cell.value
Next
End if

--
Regards,
Tom Ogilvy


"Cristian" wrote in message
om...
Hi

I am trying to generate a pivot table having the pivot fields passed
as parameters from five drop down lists.

Is there any posibility to populate the five drop down lists with the
data from Auto filters, instead of linking to a range.

Example:
first drop down list=Region(Asia Pacific, Americas, Europe)
second drop down list=Countries(Korea,China,Canada,Japan,France etc)
and so on ...

When I select Europe from the first drop down list, at this stage, the
second list is populated with all the countries , not only the
countries from Europe.(as an example, if I select Europe from the
first list, and Japan from the second list, there is no data generated
by the pivot table)

Is there any posibility to filter somehow the drop down lists, eg when
I select Europe from the first list, the second one should be
populated only with countries from Europe and so on...

Could anyone help me with this?

Thank you in advance,
Cristian