How to create a drop down list validation based on cell value

[Alvi]

Hi, I have a requirement where my data has a column with same item and another column with different widths. say as table below. Now In another cell when 'BAL' is selected, I want the adjacent cell which has the title Width to show a drop down list consisting of all the valid values of 'BAL' i.e., 6, 9, 15,18. How can this be done. kindly help

Item	Width
BAL	6
BAL	9
BAL	15
BAL	18
BAR	6
BAR	9

 

[AlphaFrog]

Do a web search for something like Excel Dependent Drop-down List and you will find several step-by-step guides. Here is a link to one...

Create Dependent Drop-Down Lists​

 

[Alvi]

Thank you AlphaFrog, I did do my web search before reaching out. In my case the list above is very big and is from a comprehensive price list and the prices are based on the widths. It is not feasible to make named list of each set of similar items.

 

[Akuini]

In my case the list above is very big

1. About how many unique items are there?

Since you're using Excel 365, please check out this tutorial from Mynda Treacy:
Easy Dynamic Dependent Data Validation Two Ways
using this method, the helper table is created via formula & you don't need to create any named range.

 

[Alvi]

Akuini, around 380 unique items with each having around 4 different widths

 

[Akuini]

Akuini, around 380 unique items with each having around 4 different widths

Well, try the method I suggested in post #4.

 

[Alvi]

Well, try the method I suggested in post #4.

I tried, it seems to work if the list is small but my Excel crashes as when I use a new sheet for using the UNIQUE/Transpose function (as shown in the video) to pull the items the list goes up to Column 'KFB'

 

[Akuini]

I tried, it seems to work if the list is small but my Excel crashes as when I use a new sheet for using the UNIQUE/Transpose function (as shown in the video) to pull the items the list goes up to Column 'KFB'

Another option:
If you're willing to use macro, have a look at this:
3-or-more-dependent-data-validation-with-vba-easy-to-set-up
there are two options: take a look at post #1 and post #12.

 

[Alvi]

Another option:
If you're willing to use macro, have a look at this:
3-or-more-dependent-data-validation-with-vba-easy-to-set-up
there are two options: take a look at post #1 and post #12.

I am not sure I can . I have download restrictions

 

[Alvi]

I tried, it seems to work if the list is small but my Excel crashes as when I use a new sheet for using the UNIQUE/Transpose function (as shown in the video) to pull the items the list goes up to Column 'KFB'

I think I got it fixed. It took a while for the calculations to complete but now it works fine. Thank you very much