Formula to return filtered table data

[Chronicide]

Hi everyone,

I'm trying to create a series of dependant drop down selections (using data validation). I can get a single drop down working by creating a single-column table of the values, then referencing the table[#data] in a namedRange, which is then referenced in the data validation (list) as =namedRange.

I now have a two-column table, the first column has categories, the second has sub-categories. Using the above method, I can add one drop down list for the categories. The first issue is this list has duplicate values (a category will be repeated for every sub-category)... so is there a way for a formula to return just distinct values? Currently, I'm using =Table[[#Data],[Column1]].

On top of that, I also want to have a second drop down that lists the subcategories that correspond with the select category.

No matter how hard I look, I can't seem to figure out a formula to return the second column's data where the corresponding first column value is equal to the value of a specific cell. If I had such a formula, I could set it into a named range, then I should be set.

So my question final is, is there a way to filter table data via a formula (without physically filtering the table... just returning a list of cells matching a criteria)?

This would work really well if excel had a 'where cluase' syntax for tables... i.e. (pseudo-code) Table1[[#data],[Column2],?[Column1]=A2]. I know this is probably wishful thinking, but I thought I'd ask the experts before just giving up

I appreciate any help anyone could offer, thanks!

- Scott

Note: I've done this sort of thing using VBA in the past, but my office won't allow VBA... so i'm trying to milk as much functionality out of an .xlsx workbook as I can.

 

[sirguise]

If you're in 2010, you could perhaps use a pivot table and use slicers as your selecters (is that even a word?). Anyway, that only works if you're not sticking those values into a formula, because I don't believe the slicer values are accessible without VBA.

Other than that, I had basically the same problem. You essentially want to build a data validation off another validation. I'm fairly confident you cannot do this without VBA. Maybe someone else on here is smarter than I, but basically you need to take the first value and pass into a filter (either pivot or std filter) in order to build the second list.

I say drag your office into the 21st century!

 

[T. Valko]

Hi everyone,

I'm trying to create a series of dependant drop down selections (using data validation). I can get a single drop down working by creating a single-column table of the values, then referencing the table[#data] in a namedRange, which is then referenced in the data validation (list) as =namedRange.

I now have a two-column table, the first column has categories, the second has sub-categories. Using the above method, I can add one drop down list for the categories. The first issue is this list has duplicate values (a category will be repeated for every sub-category)... so is there a way for a formula to return just distinct values? Currently, I'm using =Table[[#Data],[Column1]].

On top of that, I also want to have a second drop down that lists the subcategories that correspond with the select category.

No matter how hard I look, I can't seem to figure out a formula to return the second column's data where the corresponding first column value is equal to the value of a specific cell. If I had such a formula, I could set it into a named range, then I should be set.

So my question final is, is there a way to filter table data via a formula (without physically filtering the table... just returning a list of cells matching a criteria)?

This would work really well if excel had a 'where cluase' syntax for tables... i.e. (pseudo-code) Table1[[#data],[Column2],?[Column1]=A2]. I know this is probably wishful thinking, but I thought I'd ask the experts before just giving up

I appreciate any help anyone could offer, thanks!

- Scott

Note: I've done this sort of thing using VBA in the past, but my office won't allow VBA... so i'm trying to milk as much functionality out of an .xlsx workbook as I can.

Is this what you had in mind?

Lookup when there are multiple instances of the lookup value

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3