One interp on your main query and a way to get there ..

Assuming data in cols A to E, from row6 down
Cols C (change) and E (scope) are the key cols

Assuming empty cols to the right of col E,
paste the same col headers from A5:E5 into G5:K5

Then put in F6:
=IF(OR(E6="",E6="{a}"),"",C6+ROW()/10^10)
(Leave F1:F5 blank)

Put in G6:
=IF(ROW(A1)COUNT($F:$F),"",INDEX(A:A,MATCH(SMALL( $F:$F,ROW(A1)),$F:$F,0)))
Copy G6 across by 5 cols to K6. Then select F6:K6, fill down to cover the
max expected extent of source data. Hide away col F. Cols G to K will return
only the lines from cols A to E which are not either blank or contain {a}
under col E (scope), with all lines neatly auto-sorted at the top in
ascending order by the values in col C (change).
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Lisa" wrote:
Hello,
I need your expertise. I have few tabs with the similar data, where (a) is
below scope. I want to order all the variances above the scope (a) in
consecutive numbers by ignoring the blanks and below scope items. I am using
this formula but it doesn't work because it is counting a's.
=IF((ABS(C6)250)+ABS(E6)10%,COUNT($C$6:C6)&"","{ a}")

In addition, I want to use the next available number in the following tab,
so if the last item in sheet1 is 250, the next item in sheet2 would be 251.
Is this possible?

Thanks in advance,

a scope <2 & .25%
2006 2005 Change %
A B C D E
10 8 2 0.25 {a}
15 6 9 1.5 2
8 9 -1 -0.111111111 {a}
9 10 -1 -0.1 {a}