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

1
votes

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

  • not using VBA/macro
  • no or as few helper columns as possible
  • don't create separate named ranges manually, as the underlying data will change with each refresh (the source table is an OData-feed)
  • don't show empty values in the drop down list
  • cannot use the new Excel array functions like FILTER() or UNIQUE() yet
  • sort the drop down list alphabetically

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

1
excelvalidationexcel-formula
I am using EXCEL 2016 and I could not find a purely formula based solution that meets all your requirements. There is a SORT function in EXCEL 365 which might be useful in meeting your sorting requirement. Having that said, if you are ok to use a helper sheet on which you can create a pivot table based on your Odata, have the Item listed in the Row Label and sorted, filter it by PT, and use OFFSET function to dynamically refer to that range and name the function, then you use the name in the data validation drop down. If you want to see a demo let me know :) - Terry W
I am using Excel in Office365 Pro, but I don't have the SORT function (or UNIQUE or FILTER). That would make things much easier. I think it seems to work by using name range in data validation with combination of INDEX, MATCH. The problem was that I put the formula in the data validation, but I had to put it in the named range first and then use the named range in the data validation list. Also, I referred to another workbook and then formulas need an INDEX to keep functioning. The column order is also important when using INDEX and MATCH. Using PowerQuery prefiltered the column which helps. - J3FFK

1 Answers

1
votes

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 :)