I've got a dependent drop down list. It matches D2 with the column header (lists!$A$7:$O$7) for the table at lists!$A$8:$O$19 . Each column has it's own dynamic list so each list may not be as long as the others.
=INDEX(lists!$A$8:$O$19,,MATCH(D2,lists!$A$7:$O$7,0))
While testing the formula in a cell, if it matches a column that does not have data in some of the cells at the bottom, it displays a 0. This isn't necessarily an issue because the formula is used to do data validation, but when the drop down arrow is clicked, all of those extra cells are displayed as blanks and the first blank one is selected. I know I can create a blank cell at the top of each list so the top selection is highlighted, but is there a way to remove those blank selections from the bottom of the drop down list?
=INDEX(lists!$A$8:$O$19,,MATCH(D2,lists!$A$7:$O$7,0))
While testing the formula in a cell, if it matches a column that does not have data in some of the cells at the bottom, it displays a 0. This isn't necessarily an issue because the formula is used to do data validation, but when the drop down arrow is clicked, all of those extra cells are displayed as blanks and the first blank one is selected. I know I can create a blank cell at the top of each list so the top selection is highlighted, but is there a way to remove those blank selections from the bottom of the drop down list?