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

What about this?

You manage the data validation lists in the "DV" worksheet. For every new model, just add a new model in column A and then add another column for that model with the options. Don't leave any blank columns. The data validation will dynamically expand and contract based on the number of records (options) you havefor each model.

Note that I built in no logic to predicate the option selection to be based on the category (because you don't have those as dropdowns currently anyways). However, it could be possible if you need that as well.

One more note... when you change the model selection, your options will not be cleared out... so a user could end up with an invalid option selection according to the selected model. Some VBA code could be written to clear the option cells when the model selection is changed... but I am not sure if VBA is an option for you or not.

Wow! Thanks Mick & José for such quick replies - very much appreciated!

mick0005 - That looks promising, but I must admit I don't quite understand how I would restrict the options to the category they belong to. Each line is pre-populated with a different category, and the user has to select one option for each category - there are about 40-50 categories (only three shown in the sample sheet). Each option is only valid in one category - For example, in Category AA, one of Option A, B or C must be selected, but no others are valid, and option A,B and C can only be used within Category AA. I should have named the options more logically in the example - i.e. Option AA1, AA2, AA3, AB1, AB2, etc.


José Augusto - This looks a bit more automated and looks to work well. The only part I need to figure out is how to make it look up the data for the model selected in C2 0 at the moment it is a fixed reference to Model2. I will have a play and report back shortly

Thanks again to both of you for the help - You guys are brilliant!!

Formula for M column[for the first column of the table (M3:M12)]

Please Login or Register  to view this content.

Thus dropbox is dependent of Select Model in C2.
Note: Table1[#Headers]is the same of J2:K2

Thanks for the compliment

Originally Posted by bob_g

Wow! Thanks Mick & José for such quick replies - very much appreciated!

mick0005 - That looks promising, but I must admit I don't quite understand how I would restrict the options to the category they belong to. Each line is pre-populated with a different category, and the user has to select one option for each category - there are about 40-50 categories (only three shown in the sample sheet). Each option is only valid in one category - For example, in Category AA, one of Option A, B or C must be selected, but no others are valid, and option A,B and C can only be used within Category AA. I should have named the options more logically in the example - i.e. Option AA1, AA2, AA3, AB1, AB2, etc.


José Augusto - This looks a bit more automated and looks to work well. The only part I need to figure out is how to make it look up the data for the model selected in C2 0 at the moment it is a fixed reference to Model2. I will have a play and report back shortly

Thanks again to both of you for the help - You guys are brilliant!!

In your original post you didn't say that Category was also a dependency and that options would be predicated on category. In fact in your uploaded file, category wasn't even a drop down. It was hard coded, so I assumed a user typed that in, and wasn't sure whether you needed this to drive your options.

I like what Jose did

Does Jose's solution solve your problem, or do you still need to be able to use the dropdowns for Options and Category?

Thanks again to both of you - sorry for the delay in replying, Have been away at a trade show for a week.

Jose's Solution now works brilliantly - the only part I now need to solve is for the cells to autofill if there is only one option in the drop down after selecting the model.

In Jose's sheet, this would work as follows: If the user selects Model2 in Cell C2, then only one option is possible for Category AA (Option A). Instead of the user having to open the drop down menu and select this option themselves, it would autopopulate Cell C5 with "Option A".

I have seen a method that involves writing an "IF" statement for every possible option, but this would be very difficult to maintain for several hundred options. The reason for it is to make selections much faster - selecting the model will generally leave only one option for about half the categories.

Apologies about the categories in the original post. For clarity, the categories are hard coded in the real sheet, and the user has to select one option for each one. In some cases there may only be one option in that category, and in others there could be many.

Thanks again to both of you for your help with this - I really appreciate your time and effort! Being new to the forum, is there a system for saying thanks / letting others know how good you are?

Thank you for the compliments.

To thanks you thank clicking the [* Add reputation] just below the contributer.
Even more. You can, selecting the Advanced option, mark as resolved the issue, if applicable.

Thank you for the compliments.

To thanks you can click the [* Add reputation] just below the contributer.
Even more. You can, selecting the Advanced option, mark as resolved the issue, if applicable.

Thanks Jose

I still need to sort the auto-fill part, which I think might be best done by checking if there is only one valid row in the Auxiliary table for that Category, and if this is the case, making this the value in the cell, else leaving it blank and letting the user choose the correct one from the drop down list.

Effectively, for cell C5 - albeit in text, not Excel!:

=IF(Number of rows in Auxliary table where N3:N50000 matches B5 = 1, then value is that from Column O, same row, else blank)

I will also need to make sure that the formula doesn't keep resetting cells back to being blank - when a drop down menu is used to select an option, this could overwrite the formula with the new data. The User can use a fresh copy of the sheet each time they wish to do a quote, or I could possibly use a macro to put the formula back where it should be in Column C each time a new model is selected?

Thanks again for the help, from everyone who is reading and thinking of having a go.

I've sorted it - it's not perfect, as if the user presses delete when in the cell, it becomes broken, but it works. We will set the file as read only, and the intention is the user will have to re-open the file each time they want to use it, using "Save As" to store the quote if required.

The formula I used to get the last part to work is:

=IF(COUNTIF(N5:N14,B7)=1,INDEX(O5:O14,MATCH(B7,N5:N14,0)),"")

Where N5:N14 is the data in the category column within the auxiliary generated table for that model, B7 is the Category in that row, and O5:O14 is the Option Description column in the Auxlilairy table.

Thanks again to both Jose and Mick!!

No doubt there will be further questions as the project progresses, but for now I have a smug grin