trying to use the index function when #N/A can be part of data ran

I am using the index function to sample a range of data that will usually
contain several lines of #N/A data. The data is from an external app using
DDE. In using the INDEX function I always get a #N/A result even when the
INDEX is accessing valid rows of the range. If I remove the #N/A from the
data range it works fine. Problem is I need to keep the range inclusive of
any #N/A's it might be retrieving from the DDE. Any sugestions.
--
Dan T.

Perhps you can set up a dummy range elsewhere on the sheet with a
formula like this:

=IF(ISNA(A1),"",A1)

and then copy this down.

Then your INDEX function can access the dummy range (free of #N/A)
instead of the range with A1, but that range can still be updated from
the DDE.

Hope this helps.

Pete

On Aug 1, 5:32*pm, Dan T. wrote:
I am using the index function to sample a range of data that will usually
contain several lines of #N/A data. *The data is from an external app using
DDE. *In using the INDEX function I always get a #N/A result even when the
INDEX is accessing valid rows of the range. *If I remove the #N/A from the
data range it works fine. *Problem is I need to keep the range inclusive of
any #N/A's it might be retrieving from the DDE. *Any sugestions.
--
Dan T.

INDEX itself would not return the #N/A so I suspect you must use something
else within INDEX that returns the error. e.g.

1
2
3
#N/A
5

in A1:A5

=INDEX(A1:A5,3)

will return 3 whereas

=INDEX(A1:A5,4)

will return #N/A

so you must have some sort of array formula within INDEX

--


Regards,


Peo Sjoblom

"Dan T." wrote in message
...
I am using the index function to sample a range of data that will usually
contain several lines of #N/A data. The data is from an external app
using
DDE. In using the INDEX function I always get a #N/A result even when the
INDEX is accessing valid rows of the range. If I remove the #N/A from the
data range it works fine. Problem is I need to keep the range inclusive
of
any #N/A's it might be retrieving from the DDE. Any sugestions.
--
Dan T.

You are right that there is more to the formula. I suspected that it was the
index function causing the problem. It may however be the small function. I
have included the entire formula for you to look at. Thanks for the response.
=IF(COUNTIF($AT$4:$BG$50,"="&$A7)2,"",INDEX($AW$4 :$AW$52,SMALL(IF($AT$4:$BG$52=$A7,ROW($1:$48)),ROW ($2:$2)))-INDEX($AW$4:$AW$52,SMALL(IF($AT$4:$BG$52=$A7,ROW($ 1:$48)),ROW($1:$1))))
--
Dan T.


"Peo Sjoblom" wrote:

INDEX itself would not return the #N/A so I suspect you must use something
else within INDEX that returns the error. e.g.

1
2
3
#N/A
5

in A1:A5

=INDEX(A1:A5,3)

will return 3 whereas

=INDEX(A1:A5,4)

will return #N/A

so you must have some sort of array formula within INDEX

--


Regards,


Peo Sjoblom

"Dan T." wrote in message
...
I am using the index function to sample a range of data that will usually
contain several lines of #N/A data. The data is from an external app
using
DDE. In using the INDEX function I always get a #N/A result even when the
INDEX is accessing valid rows of the range. If I remove the #N/A from the
data range it works fine. Problem is I need to keep the range inclusive
of
any #N/A's it might be retrieving from the DDE. Any sugestions.
--
Dan T.

On Aug 1, 3:04 pm, Dan T. wrote:
You are right that there is more to the formula. I suspected that it was the
index function causing the problem. It may however be the small function. I
have included the entire formula for you to look at. Thanks for the response.
=IF(COUNTIF($AT$4:$BG$50,"="&$A7)2,"",INDEX($AW$4 :$AW$52,SMALL(IF($AT$4:$BG$52=$A7,ROW($1:$48)),ROW ($2:$2)))-INDEX($AW$4:$AW$52,SMALL(IF($AT$4:$BG$52=$A7,ROW($ 1:$48)),ROW($1:$1))))


You can test which portion gives the error. Select a section of the
formula in the formula bar and press F9. Then hit Escape to exit the
cell, not Enter or anything else so you don't change the formula.

sample a range of data that will usually contain several lines of #N/A data

What kind of data is in this range: $AT$4:$BG$52

You've already said it contains #N/A's but what type of data is it? Text,
numeric, both? What's in A7?

This is where you're getting the error:

IF($AT$4:$BG$52=$A7

It breaks down to: IF(#N/A=$A7 and returns #N/A.

You probably need to nest an additional IF. Something like this (depending
on what type of data it is):

IF(ISNUMBER($AT$4:$BG$52),IF($AT$4:$BG$52=$A7,ROW( ....)))


--
Biff
Microsoft Excel MVP


"Dan T." wrote in message
...
You are right that there is more to the formula. I suspected that it was
the
index function causing the problem. It may however be the small function.
I
have included the entire formula for you to look at. Thanks for the
response.
=IF(COUNTIF($AT$4:$BG$50,"="&$A7)2,"",INDEX($AW$4 :$AW$52,SMALL(IF($AT$4:$BG$52=$A7,ROW($1:$48)),ROW ($2:$2)))-INDEX($AW$4:$AW$52,SMALL(IF($AT$4:$BG$52=$A7,ROW($ 1:$48)),ROW($1:$1))))
--
Dan T.


"Peo Sjoblom" wrote:

INDEX itself would not return the #N/A so I suspect you must use
something
else within INDEX that returns the error. e.g.

1
2
3
#N/A
5

in A1:A5

=INDEX(A1:A5,3)

will return 3 whereas

=INDEX(A1:A5,4)

will return #N/A

so you must have some sort of array formula within INDEX

--


Regards,


Peo Sjoblom

"Dan T." wrote in message
...
I am using the index function to sample a range of data that will
usually
contain several lines of #N/A data. The data is from an external app
using
DDE. In using the INDEX function I always get a #N/A result even when
the
INDEX is accessing valid rows of the range. If I remove the #N/A from
the
data range it works fine. Problem is I need to keep the range
inclusive
of
any #N/A's it might be retrieving from the DDE. Any sugestions.
--
Dan T.