I am trying to do sector classification for GICS sector. GICS structure consists of 11 sectors, 24 industry groups, 69 industries and 158 sub-industries. the below link gives the sector classfication
https://en.wikipedia.org/wiki/Global_Industry_Classification_Standard
can i create a drop down list like the attached image using data validation and name range function ?
like this with a vba button to refresh
On the web there are many step-by-step guides to create interdependent validation drop-downs. This is one of them. Working your way through the instructions is serious work. To help you on your way I have created a starting point for you.
The picture shows a table named Industry_Groups. It has one column for each group, and in this column are listed all the industries in that group. Now you can create a drop-down with all the column captions which would enable you to select any of the industry groups. This drop-down would have the list specification
=INDIRECT("Industry_Groups[#Headers]")
A dependent drop-down would have the list specification given below.
=INDIRECT("Industry_Groups["&A10&"]")
As you see, it takes the referenced table column name from cell A10, which is occupied by the first drop-down. Therefore the list in the second drop-down will change depending upon the selection in the first.
Of course, whatever can be selected in the second drop-down is nothing but the name of another list in another table. Each of them would name a table like the one shown above - literally hundreds of them. They have to be created before you can start on the programming.
Talking of programming, it seems that you tried to create a VBA based system that would generate the lists on the fly. No doubt that is possible but it would require some serious programming for which your attempts do not show an aptitude. The advantage of the system I show you here is that you can create and implement it with your own resources. You know, it's the story of the sparrow in your hand and the pigeon on the roof.
