Re: problem with #value!/zero

Thanks Max
you are a genius and a gentelman!

"Max" wrote:

> "bill gras" <billgras@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote
> > ... Is there an other way with a formula
> > because the next day I have to do this all over again ..
>
> One non-array formulas play to make it dynamic ..
>
> Assume data is in Sheet1, cols A & B, data from row1 down
>
> Using an empty col to the right, say col D,
>
> Put in D1:
> =IF(COUNTBLANK(A1:B1)=2,"",IF(AND(A1=0,ISERROR(B1)),"",ROW()))
>
> Copy D1 down to say, D500, to cover the max expected data in cols A & B
> (can copy down ahead of data input)
>
> In Sheet2
> --------
> Put in A1:
>
> =IF(ISERROR(SMALL(Sheet1!$D:$D,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMA
> LL(Sheet1!$D:$D,ROWS($A$1:A1)),Sheet1!$D:$D,0)))
>
> Copy A1 across to B1, fill down to B500
> (cover the same range as done in col D in Sheet1)
>
> Sheet2 will return only the desired rows from Sheet1's cols A & B, all
> neatly bunched at the top.
>
> For the sample data posted, you'd get:
>
> 234.3 3.78
> 327 4.712
> 54 23
> 156 89.7
> (blank rows below)
>
> Adapt to suit ..
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>
>
.