Guitarmageddon
Board Regular
- Joined
- Dec 22, 2014
- Messages
- 161
Hey folks. I have a range on one tab that is driven by four dependent dropdowns. The dropdowns give the user the ability to select one of the four divisions, and then a FILTER function will lookup from a table on another tab, any of the questions pertaining to that division. They can freely select from the dropdown in any order and the filter function brings the questions over in the gray range of cells you see below.
It is filtering the adjacent "CHOICE_LIST" (A1:B18 in second screenshot) table based on the dropdowns in E16 thru 19.
How can I clean this up and instead have the filter function work off some sort of checkbox where the user can just multi-select?
My PERFECT vision for this was both a multi select box, and then VBA that would dynamically insert requisite amount of rows needed in the spreadsheet to bring those questions over (its just 4 questions per division, 4 divisions total). Instead, I have settled on a less elegant approach of dependent dropdowns and a FILTER function, but to avoid errors I have to park those dormant rows in the range so that if they end up selecting all 4 divisions, there is enough space for the filter function to bring it over...
The dropdown source data on the other tab looks like so. I have my table with whatever questions per-division. Then, I use a UNIQUE to get the unique divisions, then under that I have the list of unused divisions the dropdowns havent yet selected. Then, the dropdowns you see above are looking at that "unused list" range to give the other dropdowns what selections remain for the user (via data validation on list E6#). Im sure there is a more elegant way to do this?
It is filtering the adjacent "CHOICE_LIST" (A1:B18 in second screenshot) table based on the dropdowns in E16 thru 19.
How can I clean this up and instead have the filter function work off some sort of checkbox where the user can just multi-select?
My PERFECT vision for this was both a multi select box, and then VBA that would dynamically insert requisite amount of rows needed in the spreadsheet to bring those questions over (its just 4 questions per division, 4 divisions total). Instead, I have settled on a less elegant approach of dependent dropdowns and a FILTER function, but to avoid errors I have to park those dormant rows in the range so that if they end up selecting all 4 divisions, there is enough space for the filter function to bring it over...
The dropdown source data on the other tab looks like so. I have my table with whatever questions per-division. Then, I use a UNIQUE to get the unique divisions, then under that I have the list of unused divisions the dropdowns havent yet selected. Then, the dropdowns you see above are looking at that "unused list" range to give the other dropdowns what selections remain for the user (via data validation on list E6#). Im sure there is a more elegant way to do this?
