Introduction: Create Custom Sublist in Excel (sublist Changes As Per Header)

By

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.

Step 1: 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

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

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
and
Source as =Headers

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

Copy cells B2 and C2 over the whole column.

Step 4: 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.

Attachments