Hello All
Sorry for the long post but I think I need to offer an explanation of what I am looking to achieve and how my current solution works. My objective is to provide the user with a 'template' where they can decide which features appear in each listbox, which lists are shown in the next listbox as a result of the previous selection and which features should be excluded from appearing in any future listboxes based on the previously selected items. If possible I would like to achieve this without vba. I have attached a working example of the spreadsheet. Although it works I fear my solution is not elegant and I am looking for peoples ideas / suggestions. The final template will need to handle 100+ possible features and may have 30+ listboxes. Also, the current solution requires that the List Numbers are defined in numerical sequence. If I added feature13 and assigned it to list 2 it would not appear.
U]Explanation of Spreadsheet[/U]
(In the below explanations I will make reference to tables for convenience although I appreciate they are not defined as Excel tables)
Table1 : First table is the main control for the user allowing them to define which 'features' are grouped together to appear in a listbox. This is acheievd by entering a List Number against each feature. In the example below / attached features 1 and 2 will appear in list number 1, features 3,4, and 5 are assigned to appear in list number 2 etc. The Next List column allows the user to define which list should be displayed in the next listbox based on the selected value. In the example if the user selects feature1 in the first listbox the next listbox should display those items defined as appearing in list 2. If the user was to select feature 2 in the first listbox then the next listbox should display the items defined as appearing in list 3.
Finally, the right hand side matrix allows the user to enter exceptions to prevent features from appearing in lists based on previous selections. This is achieved by entering an 'x' in the appropriate cell. In the example if feature 1 is selected then features 4 and 11 will be excluded from appearing in any future lists. If feature 2 was selected then feature 5 would be excluded.
Table1.JPG
Table 2 : The second table is just a helper to consolidate the selected features from the listboxes and their next list number.
Attachment 567546
Table 3 : This table is used to determine if features should be excluded from future lists based on previously selected features (false = excluded, true = include). If a feature has not been selected in one of the drop down lists then it returns true. If a feature has been selected and there is a corresponding 'x' then return false, otherwise return true. The included column uses the And function to determine if a feature should be included or not.
Table3.JPG
Table 4 : This table shows the features to include in the lists based on the logic value and whether the feature appears in the next list.
Table 5 : This simply removes the blanks from table 4 using a standard approach of an array function. The listboxes use dynamic named ranges based in these consolidated lists to populate the listboxes using data validation
Table4&5.JPG
Going forwards the listboxes will appear on a userform and I will use vba to control the refresh of the listboxes etc.
Bookmarks