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

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

About: I am a hobist with lots of hobies from carpentry to mechanics. There is hardly any thing that i am not interested in.

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.

Teacher Notes

Teachers! Did you use this instructable in your classroom?
Add a Teacher Note to share how you incorporated it into your lesson.

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
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

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.

Be the First to Share


    • LED Strip Speed Challenge

      LED Strip Speed Challenge
    • Sculpting Challenge

      Sculpting Challenge
    • Tiny Speed Challenge

      Tiny Speed Challenge

    4 Discussions


    3 months ago

    Thank you artworker for your post, it helped me a lot and I am using it. After everything is working nicely and you find out that you need to add one option to a subheader, how would you add it? For example, on beverage drinks you find out that you are missing Non-alcohol drinks.


    2 years ago

    Dear artworker,

    Thank you so much for this post. It helped me a great deal. I am building an excel sheet to record my restaurant expenses. This method works like a charm and suits my need to a T. Thank you once again. Best wishes.


    7 years ago on Introduction

    If I understand what you did, the cell in column C becomes context sensitive to the contents of the adjacent cell in column that correct? Do you have a practical example?


    Reply 7 years ago on Introduction

    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.