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.

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

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.

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

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.

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

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.

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

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.

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

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.