Create Custom Sublist in Excel (sublist changes as per header)

Picture of Create Custom Sublist in Excel (sublist changes as per header)
Here I will show a process by which we can make our tables smaller and stramlined. No need to put in cumpulsory blank rows and all. The searching also becomes very simple. Just have to use the filters.
Remove these adsRemove these ads by Signing Up

Step 1: Create the Table

Picture of Create the Table
Firstly create a table! This step is optional if you don't want to put the feature in a table. We will be using the header and Sub header columns to show the operation

Step 2: Adding the Header and Sub header

Picture of Adding the Header and Sub header
In another sheet put the list of headers. And also put the list of sub headers under them horizontally.
Now select the header list and name the cells as "Headers". This will set the name for the group of cells.
In the same way mark all the sub headers. For this make sure to remove the blank spaces if any in the name or else the cells won't be named and you will get an error.
After marking check if all the marked names are available by opening the dropdown on the top left.

Step 3: Apply conditions

Picture of Apply conditions
In the main sheet (the sheet with the table) select the cell where you want the headers to appear (cell B2 in this case).
Open data validation window and set
Allow as list
Source as =Headers

In the same way set the validation on cell C2
Allow as list
Source as =INDIRECT(SUBSTITUTE(B2," ",""))

Copy cells B2 and C2 over the whole column.

Step 4: And you are done

Picture of And you are done
If everything goes right you will have a very peculiar kind of Header and sub header combination. Change the header list and see the list collection change in the sub header.

The file that I was working on is attached. You can try it out.
dchall83 years ago
If I understand what you did, the cell in column C becomes context sensitive to the contents of the adjacent cell in column B...is that correct? Do you have a practical example?
artworker (author)  dchall83 years ago
Exactly! We are using this in our office place. While creating question papers, we provide heading and sub headings which are predefined and according to the syllabus. This is done to avoid arbitrary questions being created. The headings and sub headings are created in this manner. I just used this example for showing how it can be done.