Need help using values from multiple columns to filter lists for data validation

**thewrz** · 02-04-2014, 02:49 AM

Sadly, I can fumble through VBA better than I can figure out how to do things the normal formula way.

I have tried combinations of match, vlookup, and index. But I can't seem to find a definitive source on the best way to approach my solution.

Basically, I have a fat range of data (sheet "ALL_PARTS"). With 3 columns I'm really interested in to start with.

The first column categorizes the equipment.
The second column has the manufacturer.
And the third column has the part numbers.

On a data entry sheet (sheet "EQUIPMENT"), I want to use data validation to filter the list at each stage.
screen-cap.JPG

Step one: select category.
Step two: select manufacturer (filtered down based on the category)
Step three: select a part number (filtered down based on manufacturer and category)

Step four (already figured this part out): Match the part number to values in the range and populate the columns on the data entry sheet.

I can't figure out how to do it. And I've searched on here, and I've gotten close, but when I look at people's formulas my head starts to spin.

Anyway. Attached is my Excel. With some fun VBA for pulling and sorting data. Just can't figure out how to make things work on the EQUIPMENT sheet...

AV Cost Estimate Template File - no costs.xlsm

**windy58** · 02-04-2014, 06:34 AM

You want to look at Dependant Data Validation.

**thewrz** · 02-04-2014, 03:33 PM

I tried. I could not figure it out.

I'm working on a VBA way of doing things right now and I'll post an updated file once I figure it out.

It would be nice to have a clearer understanding of how to do it in a non-VBA way... but the farther I go down the rabbit whole of INDEX( and MATCH( and OFFSET( and INDIRECT( ----- the more I find none of it really does exactly what I want. Or I need to have multiple redundant sheets of data to accomplish the lookup functions I want to accomplish.

I mean, it's "easy" if you have a couple of lists that work like the MANY examples I searched for and found on the Internet.

You have a range called "Type" and under it is "Fruit, Vegetables, Meats"

Then you have a range called "Vegetables" and under it is "Potato, Carrot, Celery"

That's fine for simple things. But I have a large range of data, with duplicate values, and multiple columns --- that I need to search through and cross-reference more like a database.

Which makes me wonder. Should I do this in Access instead?

Anyway. Blah blah. I'll be quiet now.

Need help using values from multiple columns to filter lists for data validation

LinkBack

Thread Tools

Rate This Thread

Display

Need help using values from multiple columns to filter lists for data validation

Re: Need help using values from multiple columns to filter lists for data validation

Re: Need help using values from multiple columns to filter lists for data validation

Thread Information

Users Browsing this Thread

Similar Threads

Filter multiple columns based on multiple data validation fields

[SOLVED] Activate a filter using multiple Data Validation drop down lists

Validation Lists - Multiple dependent lists with unique values

Filter pivot tables off data validation lists

Filter Two lists (multiple columns)

Bookmarks

Bookmarks

Posting Permissions