Home » Blog » Great dropdown lists in any cell

Great dropdown lists in any cell

Hello everyone! Did you know…we can create great dropdown lists in any cell.

click to INTERACT

Even better great dropdown lists in any cell

Better still we can create a second dropdown which is made to be dependent on what is selected in the first dropdown.  First of all we can key in two separate lists on a separate sheet.

List One

The first one will be headed up “Bank_In” and we input “Debtors” and “Vat Refund” underneath this heading. It is important to then name the range with the two items as “Bank_In”.

List Two

We then do the same thing for our second list which will have a “Bank_Out” column heading, underneath: “Creditors”, “Tax” and “Bank Charges” and these three items are then given the named range “Bank_Out”.

Setting up our “normal” dropdown

We select a blank cell in our main sheet, say cell D6. The set up of a dropdown takes place in the Data Validation area of Microsoft Excel which is found in the Data tab under the Data Tools group.

For Allow:   List we indicate our List Source as being the range for the two headings and make sure that in-cell dropdown is ticked.

The “special” dropdown

In the cell next to this, E6, we do another Data Validation with Allow:   List this time being a formula =INDIRECT(D6) with in-cell dropdown ticked.

The set up is now finished.

Seeing the result of our first dropdown

Clicking in to cell D6 we get a drop down of Bank_In and Bank_Out.

The linkage

Depending on which of the two items we select, this will determine which list will appear when we select the dropdown for cell E6. We get either the list associated with “Bank_In”:


Vat Refund

or the list associated with “Bank_Out”:



Bank Charges

Get spreadsheet

click to INTERACT

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.