Hi All,

I am using a Dynamic Named Range "Data", spans 55 Columns and many Rows.

I need a Formula to separately Index each Column of the Dynamic Range "Data".

The Numeric Criterion will vary.
The Data is in Columnar Format.
In the Sample Data single digits are preceded with a zero for alignment with
double digits.

I would like to find multiple instances of a Numeric Criterion in a specific
Column, and have both the Criterion and the Numeric Value that is located in
the Row above the Numeric Criterion Returned to a New Sheet in separate cells
across a Row.

Scenario:
1) Find In Column "E", the first Column of the Dynamic Range "Data", all
instances of the Numeric Value "5" (five). Then for each instance of
Criterion "5" Return To a New Sheet across a Row in separate cells the
Criterion "5" with the Numeric Value located in the Row above the Numeric
Criterion.

2) Find in Column "F" the Numeric Criterion "4" and Return each instance of
Criterion "4" with the Numeric Value located above Criterion "4".

Sample Data & Layout:
RowNo. Col"E" Col"F" etc
Row50 30 04
Row49 64 64
Row48 27 04
Row47 05 05
Row46 20 20
Row45 80 72
Row44 88 04
Row43 05 08
Row42 50 27
Row41 42 39
Row40 30 04
Row39 05 04
Row38 05 50
Row37 59 19

Expected Results - New Sheet:
1) Criterion "5"
5 5 5 30 5 88 5 27

Looking at the above Sample Layout, the Expected Results for Criterion "5"
are from Row38 & Row39, Row39 & 40, Row43 & Row44, Row47 & Row48.

2) Criterion "4"
4 4 4 39 4 72 4 64

Looking at the above Sample Layout, the Expected Results for Criterion "4"
are from Row39 & Row40, Row40 & Row41, Row44& Row45, Row48 & Row49

Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200604/1