S
Sam via OfficeKB.com
Hi All,
I have a dynamic named range "Data" spanning 8 columns and many rows. "Data"
houses numeric values.
I am using the following Formula to return 8 cells from my 18th row of "Data"
to a single column. The row of numeric values in "Data" are in ascending
order.
=INDEX(Data,ROWS(Data)-17,ROWS($1:1))
However, I would like a single Formula to return the (eight cell) results
from my 18th row of "Data" to a row in a single column that matches the value
of the result.
Using the above Formula - Expected Results from 18th row of "Data" (values
will vary):
45, 50, 57, 60, 72, 79, 84, 85
The above values should be returned to their corresponding value Position in
a single column that spans column & row N17 to N103.
Numeric Value 45 should be returned to Position 45 in my column range N17:
N103 = Row N61
Numeric Value 50 should be returned to Position 50 in my column range N17:
N103 = Row N66
Numeric Value 57 should be returned to Position 57 in my column range N17:
N103 = Row N73
Numeric Value 60 should be returned to Position 60 in my column range N17:
N103 = Row N76
Numeric Value 72 should be returned to Position 72 in my column range N17:
N103 = Row N88
Numeric Value 79 should be returned to Position 79 in my column range N17:
N103 = Row N95
Numeric Value 84 should be returned to Position 84 in my column range N17:
N103 = Row N100
Numeric Value 85 should be returned to Position 85 in my column range N17:
N103 = Row N101
Thanks
Sam
I have a dynamic named range "Data" spanning 8 columns and many rows. "Data"
houses numeric values.
I am using the following Formula to return 8 cells from my 18th row of "Data"
to a single column. The row of numeric values in "Data" are in ascending
order.
=INDEX(Data,ROWS(Data)-17,ROWS($1:1))
However, I would like a single Formula to return the (eight cell) results
from my 18th row of "Data" to a row in a single column that matches the value
of the result.
Using the above Formula - Expected Results from 18th row of "Data" (values
will vary):
45, 50, 57, 60, 72, 79, 84, 85
The above values should be returned to their corresponding value Position in
a single column that spans column & row N17 to N103.
Numeric Value 45 should be returned to Position 45 in my column range N17:
N103 = Row N61
Numeric Value 50 should be returned to Position 50 in my column range N17:
N103 = Row N66
Numeric Value 57 should be returned to Position 57 in my column range N17:
N103 = Row N73
Numeric Value 60 should be returned to Position 60 in my column range N17:
N103 = Row N76
Numeric Value 72 should be returned to Position 72 in my column range N17:
N103 = Row N88
Numeric Value 79 should be returned to Position 79 in my column range N17:
N103 = Row N95
Numeric Value 84 should be returned to Position 84 in my column range N17:
N103 = Row N100
Numeric Value 85 should be returned to Position 85 in my column range N17:
N103 = Row N101
Thanks
Sam