Having Data Validation List to ignore NA error elements?

[thraling]

Hello,
I have a drop-down list that picks its values from a known range (let's say: "A1:A8").
The problem is that this range has random #N/A that I don't want to display in the list, like:

1
#N/A
25
#N/A
4
#N/A
47
45

In this case I would like my list just to display ("1", "25", "4", "47", "45").
I cannot install plugins. I prefer not to use macros. The errors must anyway be displayed in the source range.

Is there a way?

 

[Aladin Akyurek]

Let A2:A9 of Sheet1 house the sample you posted.

In B1 enter:

=COUNT(A2:A9)

if we have to do solely with numbers. Otherwise:

=SUMPRODUCT(1-ISERROR(A2:A9))

In B2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($B$2:B2)>$B$1,"",INDEX($A$2:$A$9,SMALL(IF(1-ISERROR($A$2:$A$9),ROW($A$2:$A$9)-ROW($A$2)+1),ROWS($B$2:B2))))

Now define DVList in FORMULAS | Name Manager as referring to:

=OFFSET(Sheet1!$B$2,0,0,Sheet1!$B$1)

which you can use as list in data validation.

 

[thraling]

Genius! Thank you!

 

[Aladin Akyurek]

Genius! Thank you!

You are welcome.

 

[thraling]

Sorry, I tested it, but I'm having an error in the list:
it shows indeed the right number of elements, but only the 1 is displayed, the others are blank.
For example, if I have a list like
A
B
#N/A
C

it will show ("A", "", "", ""). I don't know where the error can be

 

[Ingolf]

Aladin Akyurek

In B2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($B$2:B2)>$B$1,"",INDEX($A$2:$A$9,SMALL(IF(1-ISERROR($A$2:$A$9),ROW($A$2:$A$9)-ROW($A$2)+1),ROWS($B$2:B2))))

This is CSE formula.

 

[Aladin Akyurek]

Sorry, I tested it, but I'm having an error in the list:
it shows indeed the right number of elements, but only the 1 is displayed, the others are blank.
For example, if I have a list like
A
B
#N/A
C

it will show ("A", "", "", ""). I don't know where the error can be

Sheet1

[TABLE="class: grid, width: 128"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent"]A[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent"]B[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]#N/A[/TD]
[TD="bgcolor: transparent"]C[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

In B1 just enter:

=SUMPRODUCT(1-ISERROR(A2:A5))

In B2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($B$2:B2)>$B$1,"",INDEX($A$2:$A$5,SMALL(IF(1-ISERROR($A$2:$A$5),ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($B$2:B2))))

Now define DVList in FORMULAS | Name Manager as referring to:

=OFFSET(Sheet1!$B$2,0,0,Sheet1!$B$1)

Adjust the sheet name to suit.

Test...

Select C1.
Activate Data Validation.
Select List for Allow.
Enter the following in the box for Source...

=DVList

Click OK.

C1 should have a list of A, B, and C to choose from.

 

[thraling]

Sorry, I missed the "and copy down" part.
Three words, completely different result

Thank you again!