Creating a NonBlank list for Data Validation

[JohnnyTightlips]

Here’s an interesting one for the forum.

Firstly, I'd rather not use VBA. Secondly, i have experiemented with Data Validation and have not found an appropriate way to achieve what I want to. If there is a simpler way to achieve it, please let me know. i fear I have overengineered this solution!!


I use a range as the validation list for some dropdown boxes. The range contains formulas with the outcome of “” if false. Therefore, when the dropdown list is selected, there appears many “blank” options scattered through the list; which are the “”s.

I am trying to create a helper column adjacent to the range that will list all non “” values concurrently, and then create a dynamic named range for use as the validation list.

The formula I am using is as follows

=index(A10:$A$25,match(1,(A10:$A$25<>,($B$9:B9)*( A10:$A$25<>””),0))

Entered as an array formula. Note the use of relative and absolute referencing: the range decreases as the formula is dragged down.

What I am trying to get the formula to do is: return the first value from the range that does not equal any previous values (the $B$9:B9 reference, which increases as the formula is dragged down) and does not equal “”.

The formula works until it finds a value after a “” reference (cell B15 -highlighted) It duplicates Fifth-Match even though the cell above it is Fifth match. i.e it appears the first match criteria hasn't worked. Can anyone help me list all non "" concurrently.

Let me know if you require clarification, i appreciate it's hard to write down a lot of contextual background in a post

Book1
	A	B	C	D	E	F
10	First-Match	First-Match			First	Match
11	Second-Match	Second-Match			Second	Match
12	Third-Match	Third-Match			Third	Match
13	Fourth-Match	Fourth-Match			Fourth	Match
14		Fifth-Match
15	Fifth-Match	Fifth-Match			Fifth	Match
16	Sixth-Match	Sixth-Match			Sixth	Match
17		Seventh-Match
18	Seventh-Match	Seventh-Match			Seventh	Match
19		#N/A
20		#N/A
21		#N/A
22		#N/A
23		#N/A
24		#N/A
25		#N/A
26		#N/A
27
Sheet1

 

[Brian from Maui]

See Aladin's revised method 1,

http://www.mrexcel.com/board2/viewtopic.php?t=8659

 

[Aladin Akyurek]

Book5
	A	B	C	D	E	F
9		0	7
10	First-Match	1	First-Match		First	Match
11	Second-Match	2	Second-Match		Second	Match
12	Third-Match	3	Third-Match		Third	Match
13	Fourth-Match	4	Fourth-Match		Fourth	Match
14			Fifth-Match
15	Fifth-Match	5	Sixth-Match		Fifth	Match
16	Sixth-Match	6	Seventh-Match		Sixth	Match
17
18	Seventh-Match	7			Seventh	Match
19
Sheet1

A10, copied down:

=IF(E10&F10="","",IF(COUNTBLANK(E10:F10)=1,E10&F10,E10&"-"&F10))

B9 must house a 0.

B10, copied down:

=IF(A10="","",LOOKUP(9.99999999999999E+307,$B$9:B9)+1)

C9:

=LOOKUP(9.99999999999999E+307,B9:B18)

C10, copied down:

=IF(ROWS($C$10:C10)<=$C$9,LOOKUP(ROWS($C$10:C10),$B$10:$B$18,$A$10:$A$18),"")

The list in column C is defined by:

=OFFSET(Sheet1!$C$10,0,0,MATCH("*",Sheet1!$C:$C,-1)-MIN(ROW(Sheet1!$C$10)))

The link quoted by Brian creates an alphabetical list, which might be more appropriate to take up.

 

[JohnnyTightlips]

Aladin: brilliant ingenuity as always,

many thanks