Hi Folks!

Trying to extract uniques from a list and make it dynamic. It works as long
as there are at least 2 entries in the list. If there is only 1 entry in the
list the formula errors but i don't understand why.

The formula without the error trap: (array)

=INDEX(List,MATCH(0,COUNTIF(A$1:A1,List),0))

List is a dynamic range name.

With only a single entry in List and A1 being empty, this is how the formula
evaluates:

=INDEX({1},MATCH(0,0,0))

Match returns #N/A when it should return 1 since 0 matches 0 and it's in the
first position of the lookup_array and the result of the entire formula
shoud be 1 since 1 is in the first position of the Index array.

Can anyone explain why this is not working?

Thanks

Biff

Biff,

Not sure why that isn't working but is it necessary to use this method?

Could you use DataFilterAdvanced Filter?

If your method is necessary please post details of the list dynamic formula
and the type of data it contains.

Thanks,
Matt

"Biff" wrote:

Hi Folks!

Trying to extract uniques from a list and make it dynamic. It works as long
as there are at least 2 entries in the list. If there is only 1 entry in the
list the formula errors but i don't understand why.

The formula without the error trap: (array)

=INDEX(List,MATCH(0,COUNTIF(A$1:A1,List),0))

List is a dynamic range name.

With only a single entry in List and A1 being empty, this is how the formula
evaluates:

=INDEX({1},MATCH(0,0,0))

Match returns #N/A when it should return 1 since 0 matches 0 and it's in the
first position of the lookup_array and the result of the entire formula
shoud be 1 since 1 is in the first position of the Index array.

Can anyone explain why this is not working?

Thanks

Biff

Hi!

Filters aren't dynamic!

There's nothing wrong with the named range formula:

=OFFSET(Sheet1!$A$1,,,COUNT(Sheet1!$A:$A))

The data is numeric.

Biff

"Matt Lunn" wrote in message
...
Biff,

Not sure why that isn't working but is it necessary to use this method?

Could you use DataFilterAdvanced Filter?

If your method is necessary please post details of the list dynamic
formula
and the type of data it contains.

Thanks,
Matt

"Biff" wrote:

Hi Folks!

Trying to extract uniques from a list and make it dynamic. It works as
long
as there are at least 2 entries in the list. If there is only 1 entry in
the
list the formula errors but i don't understand why.

The formula without the error trap: (array)

=INDEX(List,MATCH(0,COUNTIF(A$1:A1,List),0))

List is a dynamic range name.

With only a single entry in List and A1 being empty, this is how the
formula
evaluates:

=INDEX({1},MATCH(0,0,0))

Match returns #N/A when it should return 1 since 0 matches 0 and it's in
the
first position of the lookup_array and the result of the entire formula
shoud be 1 since 1 is in the first position of the Index array.

Can anyone explain why this is not working?

Thanks

Biff

Biff,

Your named range formula may be wrong. At the moment it will start from A1
but create a formula with 0 rows and (count of Column A) amount of columns.
This may be why this is evaluating to 0 in your formula?

Try

=OFFSET(Sheet1!$A$1,,COUNT(Sheet1!$A:$A),1)

Thanks,
Matt

"Biff" wrote:

Hi!

Filters aren't dynamic!

There's nothing wrong with the named range formula:

=OFFSET(Sheet1!$A$1,,,COUNT(Sheet1!$A:$A))

The data is numeric.

Biff

"Matt Lunn" wrote in message
...
Biff,

Not sure why that isn't working but is it necessary to use this method?

Could you use DataFilterAdvanced Filter?

If your method is necessary please post details of the list dynamic
formula
and the type of data it contains.

Thanks,
Matt

"Biff" wrote:

Hi Folks!

Trying to extract uniques from a list and make it dynamic. It works as
long
as there are at least 2 entries in the list. If there is only 1 entry in
the
list the formula errors but i don't understand why.

The formula without the error trap: (array)

=INDEX(List,MATCH(0,COUNTIF(A$1:A1,List),0))

List is a dynamic range name.

With only a single entry in List and A1 being empty, this is how the
formula
evaluates:

=INDEX({1},MATCH(0,0,0))

Match returns #N/A when it should return 1 since 0 matches 0 and it's in
the
first position of the lookup_array and the result of the entire formula
shoud be 1 since 1 is in the first position of the Index array.

Can anyone explain why this is not working?

Thanks

Biff

Hi!

The point of the formula is to not offset A1 by any rows. All I want it to
do is return an array of values and as I have it written that's what it
does. A1 is simply the start of the range. The end of the range depends upon
the COUNT of column A. If there's nothing in the range it doesn't matter
because there's nothing to extract!

Just to reduce any confusion, the extraction formula is on a different sheet
so the reference to A1 in that formula is not the same reference to A1 in
the range name formula.

Biff

"Matt Lunn" wrote in message
...
Biff,

Your named range formula may be wrong. At the moment it will start from A1
but create a formula with 0 rows and (count of Column A) amount of
columns.
This may be why this is evaluating to 0 in your formula?

Try

=OFFSET(Sheet1!$A$1,,COUNT(Sheet1!$A:$A),1)

Thanks,
Matt

"Biff" wrote:

Hi!

Filters aren't dynamic!

There's nothing wrong with the named range formula:

=OFFSET(Sheet1!$A$1,,,COUNT(Sheet1!$A:$A))

The data is numeric.

Biff

"Matt Lunn" wrote in message
...
Biff,

Not sure why that isn't working but is it necessary to use this method?

Could you use DataFilterAdvanced Filter?

If your method is necessary please post details of the list dynamic
formula
and the type of data it contains.

Thanks,
Matt

"Biff" wrote:

Hi Folks!

Trying to extract uniques from a list and make it dynamic. It works as
long
as there are at least 2 entries in the list. If there is only 1 entry
in
the
list the formula errors but i don't understand why.

The formula without the error trap: (array)

=INDEX(List,MATCH(0,COUNTIF(A$1:A1,List),0))

List is a dynamic range name.

With only a single entry in List and A1 being empty, this is how the
formula
evaluates:

=INDEX({1},MATCH(0,0,0))

Match returns #N/A when it should return 1 since 0 matches 0 and it's
in
the
first position of the lookup_array and the result of the entire
formula
shoud be 1 since 1 is in the first position of the Index array.

Can anyone explain why this is not working?

Thanks

Biff

Found a work around but would still like to know why this didn't work!

I put a blank, ="" , into cell A1 of the named range List. So the actual
numeric entries will now have to start in cell A2.

Also, since "" is a text value changed the named range formula using COUNTA
instead of COUNT.

Biff

"Biff" wrote in message
...
Hi!

The point of the formula is to not offset A1 by any rows. All I want it to
do is return an array of values and as I have it written that's what it
does. A1 is simply the start of the range. The end of the range depends
upon the COUNT of column A. If there's nothing in the range it doesn't
matter because there's nothing to extract!

Just to reduce any confusion, the extraction formula is on a different
sheet so the reference to A1 in that formula is not the same reference to
A1 in the range name formula.

Biff

"Matt Lunn" wrote in message
...
Biff,

Your named range formula may be wrong. At the moment it will start from
A1
but create a formula with 0 rows and (count of Column A) amount of
columns.
This may be why this is evaluating to 0 in your formula?

Try

=OFFSET(Sheet1!$A$1,,COUNT(Sheet1!$A:$A),1)

Thanks,
Matt

"Biff" wrote:

Hi!

Filters aren't dynamic!

There's nothing wrong with the named range formula:

=OFFSET(Sheet1!$A$1,,,COUNT(Sheet1!$A:$A))

The data is numeric.

Biff

"Matt Lunn" wrote in message
...
Biff,

Not sure why that isn't working but is it necessary to use this
method?

Could you use DataFilterAdvanced Filter?

If your method is necessary please post details of the list dynamic
formula
and the type of data it contains.

Thanks,
Matt

"Biff" wrote:

Hi Folks!

Trying to extract uniques from a list and make it dynamic. It works
as
long
as there are at least 2 entries in the list. If there is only 1 entry
in
the
list the formula errors but i don't understand why.

The formula without the error trap: (array)

=INDEX(List,MATCH(0,COUNTIF(A$1:A1,List),0))

List is a dynamic range name.

With only a single entry in List and A1 being empty, this is how the
formula
evaluates:

=INDEX({1},MATCH(0,0,0))

Match returns #N/A when it should return 1 since 0 matches 0 and it's
in
the
first position of the lookup_array and the result of the entire
formula
shoud be 1 since 1 is in the first position of the Index array.

Can anyone explain why this is not working?

Thanks

Biff

Hi Biff!

I think Scott has the answer. However, if I try...

=MATCH(0,A1,0)

....where A1 contains 0, the formula returns 1 not #N/A even though the
second argument is a single value and not an array. So I don't quite
understand why.

In article ,
"Biff" wrote:

Hi Folks!

Trying to extract uniques from a list and make it dynamic. It works as long
as there are at least 2 entries in the list. If there is only 1 entry in the
list the formula errors but i don't understand why.

The formula without the error trap: (array)

=INDEX(List,MATCH(0,COUNTIF(A$1:A1,List),0))

List is a dynamic range name.

With only a single entry in List and A1 being empty, this is how the formula
evaluates:

=INDEX({1},MATCH(0,0,0))

Match returns #N/A when it should return 1 since 0 matches 0 and it's in the
first position of the lookup_array and the result of the entire formula
shoud be 1 since 1 is in the first position of the Index array.

Can anyone explain why this is not working?

Thanks

Biff

Thanks for taking a look, Domenic!

I don't like not understanding things.

Biff

"Domenic" wrote in message
...
Hi Biff!

I think Scott has the answer. However, if I try...

=MATCH(0,A1,0)

...where A1 contains 0, the formula returns 1 not #N/A even though the
second argument is a single value and not an array. So I don't quite
understand why.

In article ,
"Biff" wrote:

Hi Folks!

Trying to extract uniques from a list and make it dynamic. It works as
long
as there are at least 2 entries in the list. If there is only 1 entry in
the
list the formula errors but i don't understand why.

The formula without the error trap: (array)

=INDEX(List,MATCH(0,COUNTIF(A$1:A1,List),0))

List is a dynamic range name.

With only a single entry in List and A1 being empty, this is how the
formula
evaluates:

=INDEX({1},MATCH(0,0,0))

Match returns #N/A when it should return 1 since 0 matches 0 and it's in
the
first position of the lookup_array and the result of the entire formula
shoud be 1 since 1 is in the first position of the Index array.

Can anyone explain why this is not working?

Thanks

Biff

Got it!

=INDEX(List,MATCH(0,INDEX(COUNTIF(A$1:A1,List),,1) ,0))

Biff

"Biff" wrote in message
...
Thanks for taking a look, Domenic!

I don't like not understanding things.

Biff

"Domenic" wrote in message
...
Hi Biff!

I think Scott has the answer. However, if I try...

=MATCH(0,A1,0)

...where A1 contains 0, the formula returns 1 not #N/A even though the
second argument is a single value and not an array. So I don't quite
understand why.

In article ,
"Biff" wrote:

Hi Folks!

Trying to extract uniques from a list and make it dynamic. It works as
long
as there are at least 2 entries in the list. If there is only 1 entry in
the
list the formula errors but i don't understand why.

The formula without the error trap: (array)

=INDEX(List,MATCH(0,COUNTIF(A$1:A1,List),0))

List is a dynamic range name.

With only a single entry in List and A1 being empty, this is how the
formula
evaluates:

=INDEX({1},MATCH(0,0,0))

Match returns #N/A when it should return 1 since 0 matches 0 and it's in
the
first position of the lookup_array and the result of the entire formula
shoud be 1 since 1 is in the first position of the Index array.

Can anyone explain why this is not working?

Thanks

Biff