Drop Down List - Hide invalid options & Auto-fill if only one option

[bob_g]

Hi,

I am trying to build a quotation program using excel, but am struggling to make this part work.

Using Data Validation, I have a column of drop down lists that let me pick an option from each category. This works fine, although I believe I need to keep the Options sorted by category for it to work (this limitation is OK). The Yellow cells in the attached sheet are the ones with drop down lists.

The choices in the drop down then return the price of that option in the next column along, according to the model selected above. These prices differ by model, and can be Zero, or any positive or negative number. The program then sums these prices to get the total price.

1) I am trying to get the drop down list to only show me options that are available for that model (the model is selected first). At the moment, options which are not available for that model have a price listed as "NA", but I can change this to anything as required to make the program work. (example - at the moment, selecting the drop down for category AC shows 4 options, but with Model2, there is only one valid option)

2) I am also trying to get the drop down boxes to auto-populate with the option name if there is only one option valid for that model (as an example in Model2, Category AC, only "Option G" is possible, and I would like this to auto populate when the model is selected.

Although the sample shows only 2 models and 10 options, the real data could be up to 50 models and 1000 options or so, and needs to be relatively straightforward to update, maintain and add to, so am trying to avoid solutions that rely on individual exceptions.

Any help would be very much appreciated - thank you for taking the time to read this!