Macro to select a value from a validation list

[EldrckTW]

Hi people smarter than me !

I've got a very complex sheet with a ton of validation lists. These lists have many dependent variables, and when it comes time to select from a specific list, there are usually only 1-2 options to choose from.

I would like to create a macro (or some other solution) that selects the first value in the validation list when only 1 value exists to be chosen. This would save the user A TON! of time populating this sheet as hundrends of validation lists could be on here.

My only idea was to write a formula (not VBA) in the Validation list cell that had the same rules as the Validation List to display a value if one option was shown. As there are tons of depenedent variables....this would mean writing a lot of multi-celled formulas (multi-celled because the IF statements would be longer than Excel would allow).

Any ideas? Appreciate the help!

 

[jbeaucaire]

Actually, you said the "drop down" in the final cell usually only had one or two options. What is that DV formula?

You should be able to put a "cell" version of that formula in a cell with an IF/THEN that counts the number of options in the resulting array.

In this simple example, the first named range NM1 only has one item in it, the next has 3. The final formula out in H3 shows how you can test that and display that one option using just a formula.

Excel Workbook
	A	B	C	D	E	F	G	H
1	1 cell rng	3-cell rng
2					Name:	Entries		Formula
3	cat	bird			Nm1	1		cat
4		dog			Nm2	3
5		cat
6
Sheet1