Need help to make a dynamic drop down list, that extracts the values with criteria.

I have a range of thousands of data with many attributes(columns). I use a single row per item. Now what i wanna do, is to allow the user to pick one of those items(choosing by name) in another sheet. But because they are too many i want to let him "filter" them first by choosing a category from a drop down list.

I cant convert that range into a table, and i tried sorting all that data into some other columns and defining those columns-names and with INDIRECT make the second list show the defined name that the first has chosen. But because there are about 10.000 items with more than 45 attributes this gets my excel sheet extremely slow.

So i want to extract those items directly from the range if able. Or to use another less "heavy" way.


Heres a small example of what i am trying to do. I have an array with data :
Screenshot_2.png

Now i will extract the "categories"(DOG,CAT, HORSE) into some other sheet. i will define them dynamically into a name. (i want from the user to add any category he likes later into the hidden data-sheet with a VBA that inserts a row )

And then in another sheet i will make a drop-down list with the categories and i want from the cell next to that to show a drop down list with the pets name from the category that i just chose. In fact in that sheet there will be several of those double drop down lists one directly below the other.

Thank you all !

What you want is known as a cascading drop down list. You can do a web search on the term and get plenty of examples.

If the data is such that an item is unique to a category, that is, dog only shows up in category animal and not in category plant, then there is a non-vba method.

If not there is a VB method that works for all category-items parings.

Here are the articles that describe each method.
http://www.utteraccess.com/wiki/Casc..._%28Non-VBA%29
http://www.utteraccess.com/wiki/Casc...ists_%28VBA%29

I actually found another way ! THank you !