Data validation List based on another DV list

Hello Everyone,

I need help to make a function to create a validation list based on another validation list text selected.

Please find attached the excel file with the information on sheet 3 (Sells) about the function i need.

Sheet 1:
I have my DB with a table of "ProductType"

Sheet 2:
I have my stocks with a column of list "ProductType" that i can select, and another column with the description of the product.

Sheet 3:
I have a selectable list of the Product type based on the "ProductType" table on sheet 1.
I have another column "Product List" in which i want to put a data validation list based on the product type selected on the "ProductList" column.

Please help me to find a function to resolve my issue.

Thank you

Regards,
Vij401

Hi,

I tried all the solutions presented on the link but i'm not able to make what i need.
Please help me with the function.

Thank you by advance.

Hi,

Find the attached sheet with your requirement.
Note: In Sheet2(Stock) I have rearranged the Product Type and Description in the range D to G(green Highlighted portion)
to facilitate the data validation list. Do not delete that portion as it is linked to the list.

Hello,

Thank you for giving me this solution.
Unfortunately, i can't use this solution because here i have to create a new table and i have thousands of different products description, so it will be difficult to manage it.
Is there any other solution to make the same without creating this new "green" table ?

Thanks & Regards.

Hi,
The Green region has formula that derives all the product description based on the mentioned Product type.
I have updated the formula in the Green region for accommodating 500 rows of data also have dragged the formula till D500:G500.You may drag it further if required.
As an example, I have added sample data by adding a number at the end of the Product Description to display in the list.
You can add data in the Column A and B in Sheet2(Stock) and drag the formula correspondingly in Col D:G
Note :I have also added the formula that is fed in the data validation for your reference in Sheet3(Sells) Cell F2.
Check if this version to meets your requirements.

Hi HariArvi,

I understand your solution and i thank you for that, but i need a solution without creating any other table.
I want to create the dynamic DV list on Sells Sheet based on the new line created on the table of Stock Sheet.
Is there any solution by adding directly a formula on Sells Cell "Product List" ?

Thank you for your help.

Hi,

I have updated the sheet for DV list in Sells without Supporting table.
Note: The table in the Stock sheet needs to be sorted once you add any data.
Hope this satisfy your requirement.

Hi,

This is indeed the type solution i needed.
But there is no solution to make it without sorting the Stock table ?

Thank you

Hi,

The formula works only when the table is sorted.

Hi,

Yes i understand that.
Is that mean we have not any other solution ?

Thank you

With the experience that I have, this is the solution I am aware of.

Thank you for this solution.

If anyone else have a solution without sorting the table it will help.

If you have Office 365 then you can use this in E2 (copied down):

=TRANSPOSE(FILTER(Stock[Decription Product],Stock[Product Type]=Sells[@[Product Type]]))

Then for the Data Validation in C2 use:

=$E2#

I don't think you can have a dynamic array for data validation without creating the reference somewhere.

Hello,

I don't have problem to use VBA code if that's the only solution possible with MS Office Excel 2013.
However, on your solution i have a little issue.
When i add a new line, the Product List is not filled automatically with the Stock Product Description. To make it fill, i have to manually change the range(C2:C4) to range(C2:C5) on your code.
And if i put range more than the existing table or the even if i create a bigger table, i have an error on the code.
Could you please verify and send me a dynamic solution?

E.g:
1) If i add date and product type on line 5, and i click on C5, i should see the product list associated to product type automatically without going on code to change the range.
2) If i add date on line 5 but not yet the product type, and i click on Product list, there should be no error. And after if i add the product type, the process should be as 1).

I hope it's clear.
Sorry but English language is not my first language

Thank you for your help
Regards,
Vij401

Hi,

The product list fill now automatically without having to change the code.
But we have two kind of errors ;
1) When we add a new line, but the "Product Type" is not yet selected and i click on Product List cell of that product tpye, i have a Error 1104 on the code.
2) Same if i click on a empty cell of Product List column, in which i don't even extended yet the table, i have an error also.

Please give me a solution in which i don't have this error.

Thank you by advance.

Hi,

It's ok i will use ";" instead of ",".
Thank you for your help

This post can be close (i don't know how to close it, it's my first post).