Dynamic range in one column based on value in other column

[MichielD]

Dear all,

I have a relatively simple challenge to add a dropdown, with a dynamic range, in Excel that shows values of column A that meet a criterium in column B.

Column A Column B
1 Mark - Tree
2 Richard - Tree
3 Mary - Apple
4 Joe - Tree

In the example above, I want the dropdown to filter column B on "Tree" and provide me with Mark, Richard and Joe.

The Offset, Match formula only works for me when all items with "Tree" are sorted, but thats not the case.

Hope you can help me.

Michiel

 

[bbott]

Welcome to the board.

Have you tried just using filters?

 

[MichielD]

Thanks Bbott!

Filtering sounds good, but how would I use a filter in a dropdown?

 

[ameydabholkar]

Hey,

If you want to do it with a combobox then here is a tutorial Auto Populate second Combobox: Auto Populate second Combobox - YouTube

You can also do the same with a huge enough offset dynamic formula and data validation
Regards,
Amey

 

[bbott]

Sorry, I think I misunderstood your question. So you want the filtered results to populate a dropdown?

 

[MichielD]

Thanks Amey! I would indeed be interested to learn how to construct this advanced offset formula.

That's right Bbot.

 

[MichielD]

Any solution you would be able to provide me for this issue?

 

[bbott]

Any solution you would be able to provide me for this issue?

Sorry Mchiel, I don' have a good solution for your issue. The only thing I can think of is using VBA.