DV Update List Product on Sells if Stock is 0

Hi,

I have a file with 2 sheets (Stock & Sells).
On Stock sheet, i have a table with all my products and the remaining stock.
On Sells sheet, i have a table with a column "Product" in which i want to make a data validation list based on Products available on Stocks.
If Product on Stocks are available, i want it to appears on DV list, but if Product is sold and product on Stock is equal to 0, i want that the product doe not appears anymore on futur DV list on Sell sheet.

Please find attached my excel file.

Thank you for your help.

Regards,
Vij401

Hi,

Thank you for this solution but i don't want this kind of solution.

Also i have thousands of lines, so i think this will be maybe complicated to manage.

What I want is to add a formula directly on Sells Product column, without creating a new table.

Like the formula you used (=SIERREUR(INDEX(StocksTab[Product];PETITE.VALEUR(SI(StocksTab[Final Stock]>0;LIGNE(StocksTab[Sells])-1);LIGNE(A1)));"")); but directly by putting this on DV.

Do you think there is a solution for that ?

Thank you

Hi,

Ok!
Sorry, it's not "complicated" but not esthetic.
Instead of adding a new range, is that a possibility to integrate a new column to the Stock Table with dynamic cells like "Available/Not Available" ?
And use that new column to make our DV on Sells ?

Thank you

Hi,

Sorry for the word mistake, english is not my first language.

The difference here is to create a separate unique column table, what i don't want to do.
What i want is to use the current Stock table, if necessary i'm ok to add a new column on it, but i don't want to create a table with one column only.
But don't worry if you don't have any other solutions except vba.

Anyone else have the solution i need ?

Thank you for your support.