Data validation list that is reduced based on a previous cell selection

Hi, any help would be appreciated! I have attached a spreadsheet in case anyone wanted to take a look at it.

On Sheet2 in column H I have the name of suppliers. Column E features the product they sell. However the same supplier (eg Amazon) will feature many times in column H, showing the different products they sell on new rows. The suppliers are not in any specific order in Column H, and some suppliers will eventually feature up to 50 times over 1000+ rows.

On Sheet3 I have a purchase order form. At the top of the purchase order the user selects a supplier (such as "Amazon"). After they select a supplier there are 10 rows where they can then select products they require from a drop down.

I would like these 10 rows to only allow a user to select products sold by the supplier they chose at the top (ie not 1000+ options from ALL suppliers!). Can this be done? I would happily insert VBA if that was the solution, but don't know how! Thanks if you can help, I have been reading everywhere for 2.5 days but can't see a solution anywhere.

Thanks Davesexcel; I have added those details as requested.

ascool_asice; I appreciate you looking at this a huge amount. I downloaded your file (with macros enabled) but when you change the supplier it only ever offers item 8 and item 9. I appreciate you looking at it so much, do you think I am doing something wrong at my end?

Hi,

Not sure why its not working on your PC/laptop.. you might want to check your trust center settings

File->Options->Trust Center->Trust Center Settings

I have my settings to enable all macro's...

you can see the file in action https://www.dropbox.com/s/cw0ic1l630...30-01.flv?dl=0

Gents, that is phenomenal. I learn so much browsing these boards! Thank you for your help.

There is one bit of code that generates an error which may have a simple solution... but I just don't know enough! It is bold below:

Please Login or Register  to view this content.

If happens if you select a supplier from the list but they have no "Regular Items" for sale. A "runtime error 1004 - No cells were found" message comes up upon selecting the supplier. The issue is that sometimes a user of this sheet may want to select a supplier from the list, but only to add "one-off items" on the order form... so they will not need to use the regular items section of the form. Is it possible to get rid of that problem so that either no debug message comes up?

Either which way, your work has left me even more aware of how little I know! Thanks so much.

Hi Dave,

I'm away from Excel as I'm going away now for a few days. I don't want to trouble you further but the problem is as follows. When I downloaded your sample file (thanks) if I ever selected a supplier who had no "regular items" listed it generated an error message as described in my previous post. I saw that you added some code in a message AFTER the sample was uploaded, so I tried replacing the original code and I also put that in as a new sub. It generated an error message on the line:

Please Login or Register  to view this content.

I know the problem will be that the code you gave me AFTER your file upload hasn't be attributed to the right place BY ME... but I can't work it out and I'm way out of my depth!