CODEWITHSUNDEEP
excelvalidationexcel-formula
J3FFK
J3FFK

Reputation: 684

Excel Filtered Data Validation from other Table not using macro and helper columns

How can you create a pre-filtered data validation drop-down list in Excel, while:

This question comes close, but I cannot get the answer to work in my case. This answer uses an array, but when I use the formula as shown, the result is empty:

{=IFERROR(INDEX(Table1[Item];SMALL(IF(Table1[Type]="PT";ROW(1:5);"");ROW(1:5)));"")}

The OData feed could be pre-filtered using PowerQuery (DAX) perhaps, but I'd like to know how to accomplish this in regular Excel formulas.

The table looks like this:

enter image description here

The desired drop down list shows every Item with Type = "PT", so that would be value: DEF, KLM and RST

enter image description here

Upvotes: 1

Views: 595

Answers (1)

Terry W
Terry W

Reputation: 3257

Gave another go this morning and I was able to work out a solution with 2 helper columns.

As shown below, the first helper column is to find the Item names with corresponding Type "PT", and the second helper column is to sort the result alphabetically.

Here are two limitations to this solution:

1) the Item name must not be purely numeric (such as 123 or 456);

2) for a given Type, there cannot be duplicated matching Item names (eg. there cannot be two Item named ABC that has PT as type) .

Solution

Before showing my formulas, I need to define a few Names as below:

Item - It is the name for Column A in my solution;

Type - It is the name for Column B in my solution;

Filter - I have hard-coded it as "PT" in Name Manager. It can be stored in a cell if preferred;

Height - It is a dynamic value counting the number of Items that meets the given Type. It is used as the height reference in the following OFFSET formulas in my solution.

Filtered - It is a dynamic range within Column C (Helper 1) capturing all values except blank. The formula is =OFFSET(Sheet1!$C$1,1,,Height,);

List - It is another dynamic range within Column D (Helper 2) capturing all values except blank. The formula is =OFFSET(Sheet1!$D$1,1,,Height,).

Name Manager

The formula in Cell C2 is as below. As it is an Array Formula you need to press Ctrl+Shift+Enter aka CSE to make it work. Just drag it down to apply across board.

{=IFERROR(INDEX(Item,SMALL(IF(Type=Filter,ROW(Item)-1,""),ROW()-1),),"")}

The formula in Cell D2 is also an Array Formula and you can also drag it down once entered with CSE.

{=IFERROR(INDEX(Filtered,MATCH(ROWS($1:1),COUNTIF(Filtered,"<="&Filtered),0)),"")}

Lastly, use List (the pre-defined name) as the source for your data validation as shown below.

Data Validation

Conclusion

I think the above solution is probably not very practical especially when there is large volume of data, however I enjoyed learning using SMALL to locate a range of data conditionally, and using COUNTIF to sort text value alphabetically.

I also noticed the limitation of OFFSET that it cannot be used for a dynamic range stored in a name as there is no valid starting cell or a range of cells. Otherwise there may not be any need of helper columns.

Lastly, I think the best approach to this question would be Power Query, which can filter and sort the required range of data quite easily for even a large volume of data. And also there is a way of using Pivot Table to filter and sort the required list if adding a helper sheet is an option. Cheers :)

Upvotes: 1

Related Questions