Populate dropdown list based on value in adjacent cell

[ajnaaslam]

Hi,

I have a workbook with two sheets. The first sheet contains information as below:


Column A------Column B
Orange----------Fruit
Carrot-----------Vegetable
Apple------------Fruit
Banana----------Fruit
Cauliflower------Vegetable


I have a drop-down list in another sheet in which I need the list of Fruits only. How do I populate this list? Please help


P.S. I have tried advanced filters, but the list wont get updated when any changes are being introduced, and I am not supposed to use any macros in this particular workbook

 

[mick0005]

Hi Ajnaaslam -

Here is the solution I use for this kind of application. I just built it up once and now I generally pull it up and build off of it each time I need to re-use it for cascading drop-down applications. Note that the limitation for this particular setup is that it is only easy to do for 2 criteria. A similar method can be used for 3+ criteria, but the maintenance is much more manual and can't be done using the format you're using on your sheet 1.

You will maintain your data on sheet 1. Each row needs to have 1 value for the selection 1 drop down (fruit or veg in your case) and 1 value for the corresponding options that will appear in selection 2 (name of the fruit or veg) in your case. If you want the predication to be swapped, i.e. select fruit/veg name first and then be presented with the category, just swap the values in the setup.

This list will drive some formulas on the "DV Lists" sheet. You don't need to touch these, they are automatic based on your entries on sheet 1. If you need to extend some of the formulas because of the size of your data, that isn't a problem, they are capable of being dragged down or across. Just note where the formulas are different.

This is all driven using named ranges as the source for the data validation lists. Please take a look at the defined ranges to see the formulas if you are curious.

File available at:
https://www.dropbox.com/s/27t4cj82h1d4w4u/Cascading DV_Ajnaaslam.xlsx?dl=0

 

[L. Howard]

Hi ajnaaslam,

You could also add this change_event to sheet 2 module in mick0005's example sheet.

Where, when an item in drop down B2 is selected it will clear the previous entry in B3 and select B3.

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
  Range("B3").ClearContents
  Range("B3").Select
End Sub

 

[mick0005]

@L. Howard, thanks for this addition. I meant to make a note that this method would not do anything to prevent an invalid selection in Selection2 if Selection1 was changed after the fact. I'm using similar code in my own workbook to do the same thing you suggested.

 

[ajnaaslam]

Thank you very much for your responses L.Howard and mick0005.

In the workbook, Sheet2 contains two dropdowns - first to choose the category (Selection 1), and the next one to choose the item (Selection 2)

The thing is, I already have separate sheets for Fruits and Vegetables; so is there any way I can get the list of fruits/vegetables without having to choose from Selection 1? Hope my query is clear.

Thanks again for your help. Much appreciated.


Ajna

 

[mick0005]

So what would drive the selection 2 list then? Maybe I'm not clear what you're asking for. Do you literally just want seperate drop downs, one which shows the fruit results and one which shows the vegetable results? If so you can easily just change the reference in the named ranges for the DV to be hard coded to "fruit" and "vegetable" instead of pointing to the value in Selection1 drop down.