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.
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.
Hi Sir,
lastly
The category for on-time doesn't work, other category are okay now.
Julius575 Sorry! missed than one. Named ranges may not start with a number, contain spaces or any special characters. Such starting numbers will be preceded by an underscore. Spaces and special characters will be replaced by an underscore. So, change the header "On-time" to "On_time". Then it shall work.
- Hi Sir,
It works!
Thank you so much for your help.
Appreciate it!