Hello everyone! Did you know…we can create great dropdown lists in any cell.
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”:
Debtors
Vat Refund
or the list associated with “Bank_Out”:
Creditors
Tax
Bank Charges