Create custom dropdown list using data validation

I am trying to create a custom dropdown list based on car manufacturer and color of car. In column A I have a list of 19 car makers. In column B I have the color of each car. I want to create dropdown list for only the car makers for which the car is gold. Is there a way to do this. Example spreadsheet attached.

Not quite what I was looking for, but close. I'm trying to create a "dropdown" given my criteria. Please see attached and select cell H2. You will notice that H2 has a dropdown option attached to it. This is what I trying to achieve based on the criteria for columns A and B. Thanks again.

maybe so (see attached file)

Thanks for the comment but is there a method for soloving without having to create additional columns of data to reach the end result? Thanks.

you can try to use the Worksheet_Activate event

How would I expand the dropdown to include range F2:F25? Right now it only applys to cell F2. Thanks for your patience.

сhange this code

Please Login or Register  to view this content.

It is working but it brings up another issue which is the list is dynamic, meaning the at anytime there can be more or less number of Car Models in the list and the car color may also change. When this occurs, the dropdown does not automatically update. Thanks