You may have moved on from this one, but in case it's still eating at your
brain ...
I think it failed because the second argument in =MATCH(0,0,0) is looking
for an array, not a single value.
To test I entered just =MATCH(0,0,0) in a cell and it returned #NA, but then
I entered =MATCH(0,{0},0) in another cell (I manually typed the {}) and it
returned 1.
HTH
Rgds,
ScottO
| 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
|
| | > 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
| >
| > | >> 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
| >>>
| >>> | >>> > Biff,
| >>> >
| >>> > Not sure why that isn't working but is it necessary to use this
| >>> > method?
| >>> >
| >>> > Could you use Data>Filter>Advanced 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
| >>> >>
| >>> >>
| >>> >>
| >>> >>
| >>> >>
| >>> >>
| >>>
| >>>
| >>>
| >
| >
|
|
