I would like to create a drop-down list from another worksheet in the same file. The other worksheet has a number of items listed, and some criteria that may exclude the item from being useful in my drop-down list.
What I have done is start a new column that has the following type of formula: =IF(E3>0,D3,"") where D3 is the name of the item. When I make this into a named list in order to reference it on another worksheet, I get a number of blanks in my drop-down list.
I have found this resource: which explains how to dynamically lengthen or shorten the number of items in the column, but what I want to do is actually just skip the blank items. The column length will stay the same, but how many items are valid is what changes.
Sorting the columns such that blank items come at the end is not an option, unless there is a way to dynamically conduct this sort. The reason is that as the spreadsheet is updated, the result of the "if" formula will change, and some items that were valid will no longer be, and some that were not valid list items will become valid. Thus I need the items available on my list to respond to these formulae. Ideally, if an item is no longer valid, it would reset the drop-down list to the top item ("Select", which is not a list item that changes), but this would just be icing on the cake.
1 Answer
After more searching, I found the following resource that gives an answer, when combined with the resource in my original question:
Using structure along the lines of =iferror(index(range,small(if(istext(range),row(range),row(A1))),"")one can construct a list without the empty cells, where range is the list of interest. Then the previously mentioned link can be applied, where the list dynamically adjusts to the length (key is using offset() to do this).
The other adjustment that needs to be made is that we cannot use counta() to determine which cells are empty or not, because the empty string "" reads as non-empty. Thus we need to subtract countblank() which does read "" as blank.
