Forum Discussion
How to Create LOV with category with corresponding sub-category
Julius575 Added a workbook with two examples of Data Validation. The first one uses a structured table with all the options and dynamic array functions UNIQUE and FILTER. The second example uses a more traditional method with named ranges, though it's less flexible and requires more maintenance.
i tried to use the alternative, i added categories but it couldn't reflect the subcategory that i added, hope you can help me with this.
i used function INDIRECT in the data validation for sub-category.
Julius575 But did you create named ranges for x,y,z, etc? And better to change the sub-categories in the example. Then you can at least see what you are looking at when you click in the drop-down in the cell next to "y". For example, use sub-categories y1 to y5, and z1 to z5.
i already did to change sub-categories,
when i do the data validation, there's an error occurred pls see below screenshot
Hi Riny,
Attached file, already plot data for sub-cat in the "option" sheet.
kindly check "selection" sheet on the sub-category with the red-highlight it doesn't shows LOV for sub-cat.
Julius575 Fixed. Although you created the various option lists, you did not create Named Ranges for them. I did that in the attached file. The easiest way to do it yourself is the select a list with options including the header, and then on the Formula Ribbon in the Define Names group click on "Create from selection" and confirm that the name of range is in the top row.
Repeat this for every list. You could select the entire range with options (incl. the headers) and do it all at once, but than you include blanks at the end of all lists that are shorter than the longest one. You can click on the Name Manager symbol to view all named ranges in the current workbook.