Written by:

Written by:

Thomas Szigeti

Contextual drop down menus in excel

In this post I will show you how can you make contextual drop down menus in excel.
(When the content of the forst drop down menu governs the content of the second drop down)

Sometimes this technique also called cascading drop down menu excel

Define your named ranges from selection.

You will have to setup your data in a specific way so the left hand side column will be the name of your named ranges that will span from column B to the right.

If you look Carefully the Named Ranges changing your team names.
In Named ranges you not allowed a space.
So the spaces in Column A will be automatically converted to the _ character

First you need to set up the named ranges

    Set up the first List

    You need to set up your first dropdown menu anywhere you want it.
    We simply pick a cell and add the List through data validation.

    Set up the reference to the second list

    Now that we have our first Drop down menu ready, we will write the formula to a cell that we will delete later.
    We will use the SUBSITUTE formula.

    What the SUBSTITUTE formula does?
    It looks for a specific character and replace it with another in a cell value.
    We will replace the space in the team names with an underscore _.

    Then we will wrap it in an INDIRECT formula.

    What the INDIRECT formula does?
    It uses a piece of text string as a reference.
    We will use it to make our team name with the Underscore to be treated as a reference.

    Once we write this formula you will see that it evaluates to an error.
    Don’t worry! This is expected.

     

    The formula in cell J3 will be:
    =INDIRECT(SUBSTITUTE($H$3,” “,”_”))

    IMPORTANT TO NOTE!!!
    I locked the H3 cell both ways!

    Copy the prepared formula to Data Validation
    We click in to the cell with the formula we wrote
    Go to the formula bar and copy the whole formula.

    We go to the second dropdown cell.
    Add a data validation, select list, then paste the formula in as a reference for the list.
    After we done we can delete the prepared formula from cell J3.

    Come and visit us.

    Come and visit excelangel on Facebook to find and join the growing community of true office workers.

    Oh, I almost forgot.
    Drop us a like while you there…

    [et_pb_ccfcm_facebook_comments_module ccfcm_app_id=”339898187197719″ _builder_version=”4.6.1″ _module_preset=”default”][/et_pb_ccfcm_facebook_comments_module]