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

"Biff" wrote in message
...
| 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
|
|
|
|
|
|
|
|
|
|
|
|
|