Named Range, using a variable to determine the last row that does not have #N/A

[USAMax]

I created a lookup table from a much larger table of data.

The first column returns unique values. I use Data Validation to choose one of these values.

The second column lists unique values that match the selected value from the first column but some of the values will have #N/A.

How can I specify the valid data range in a Named Range without the #N/A?

or

Is there a way to get the data validation to ignore the #N/A values?

or

Is there a way to get Excel not to display the #N/A so it does not show up in the data validation?

 

[ca.moazzam]

Use IFERROR function

 

[USAMax]

Thank you CA, but how would I use that in Data Validation when I select the source for a list?

 

[ca.moazzam]

You modify your existing formula like this: =IFERROR(...............,"") where ........ is your existing formula. Please note that without having sample data it is difficult to explain.

 

[USAMax]

In the Data Validation's source box I tried:

=IfError("",Division)

and

= Not IfError(Division)

The first error said, "The list source must be a delimited list, or a reference to single row or column." And the second error was very general.