Data validation lists are a great way to control the inputs to your worksheets and having one list dependent on another is nothing new.
I did a post on how to do this using a named range trick.
This means if you add another list of data in column F it will also be automatically included in the data validation lists.
Step 2 is much like step 1 in that you set up another named formula. This named formula is used to generate the validation list for both the ‘Categories’ in column A and the ‘Choice’ in column B.
This formula behaves differently depending on which cell you have selected and the value in the cell immediately to the left.
But, Roberto’s solution is better because it’s dynamic, and that means you can change the names of the lists, or add new items to the lists and it will automatically include them in the data validation list like this: Let’s take a look at how Roberto uses some clever Named Formulas with data validation to dynamically generate the lists.
The lists are on a sheet called tbl_data in cells B2: E7. This is the primary list (it will be called ‘Categories’) and rows 3:7 contain the secondary lists (they will be called ‘Choice’). To do this go to the Formulas tab of the ribbon and select ‘Define Name’. The INDEX and COUNTA functions in the formula count how many cells contain data in row 2, whereby generating a dynamic range for the named formula.
This can easily be fixed by adding the rule, ‘display: inline;’ to the header div element, as demonstrated in the code below: You should use the ‘clear’ property when you have elements following a floated element in the source code that must remain below the floated element visually on the page.