Data validation dropdown list in cell IF...

Hi,

I have followed tutorials I found using Google in trying to improve how my sheet works but all I can find seems to be working for a horizontal movement between cells and my data runs vertically. I have a list of barcodes, column A sheet "transactions" and next to that, column B a list of item descriptions. Column C is a column for Variants because sometimes an item has the same barcode but is different in some way (for example, there are items of different colour all under the same barcode, so the description is the same but the variant for "yellow", "blue", or "green" is what makes the entry unique for stock counting purposes).

What I would like to do is make the variant column a dropdown menu, with relevant selections for the barcode that is scanned into column A of sheet "sales". This would mean that when a barcode is scanned that contains no variants, there are either no options in the dropdown, or there is no dropdown menu available but when there are variants available for that code, the dropdown menu shows only options that are relevant to that barcode.

I'm flexible with my database at this point, so if the solution requires that all non-variant cells be empty for example, I can arrange that but at the moment a product without a variant contains a 0 in the variant column.

-Jay

Hi Bmouse.

There are hundreds of barcodes and the list will only ever increase in size really. I have considered lots of different ways to accomplish this effect.

In essence the validation needs to look at the barcode and pick out all the possible variants for that code, then populate a dropdown list with them.

In the various tutorial pages i went to, there was only ever a description of how to get multiple dropdown menus that were dependant on cell entry but they weren't exactly what I would need as they don't ever change the list of results based on the value of the cell that they look at.

I tend to agree bmouse. I don't think i can easily do what I described above without some massive effort. However,

just reading back to myself what the problem is I think i have come up with a method.

I would find a blank space on the worksheet and in one cell put a simple link to the column B cell eg, "=B9". then in column C I would use a formula to lookup all unique variants that are listed alongside that code. I've done a "list unique" type formula before.

From there, I would make a data validation list using the unique values list that is created each time the value in colB changes and presto! I have a dropdown in column C that changes depending on which barcode I have selected! The macro that processes the sales won't care where that data is found, all it cares about is whether the variant name exists so that it knows where to deduct stock from after a sale - which it would because the variant dropdown and the stock sheet are sourcing from the same list!

I'm going to give this method a try later and see if there are any pitfalls that make it impractical to use as my solution

I tend to agree bmouse. I don't think i can easily do what I described above without some massive effort. However,

just reading back to myself what the problem is I think i have come up with a method.

I would find a blank space on the worksheet and in one cell put a simple link to the column B cell eg, "=B9". then in column C I would use a formula to lookup all unique variants that are listed alongside that code. I've done a "list unique" type formula before.

From there, I would make a data validation list using the unique values list that is created each time the value in colB changes and presto! I have a dropdown in column C that changes depending on which barcode I have selected! The macro that processes the sales won't care where that data is found, all it cares about is whether the variant name exists so that it knows where to deduct stock from after a sale - which it would because the variant dropdown and the stock sheet are sourcing from the same list!

I'm going to give this method a try later and see if there are any pitfalls that make it impractical to use as my solution

Here is a simple table that allows you to enter barcodes as column titles and the variants to be added under each code. As codes are added to the title row of the table, they are automatically added to the drop down listing with codes and the variants are added to the drop down list associated with the barcode.