dynamic transpose

[BorisS]

Is it possible to do a dynamic reference for a transpose?

I'd like to do the transpose of a 12 cell horizontal reference into
vertical, but I need the tranpose function to lookup the line of the 12 cells
by finding a value in a vertical list. In other words, the value that drives
it all is in a list. The 12 cells are to the right of the found cell. So
the vertical block of 12 cells (which would be the transposed values), would
have to do the following:

find the lookup value
grab the 12 horizontal cells that start about 10 cells to the right of the
found value
transpose those 12 cells into vertical.

Thx for any help.

 

[Leo Heuser]

Is it possible to do a dynamic reference for a transpose?

I'd like to do the transpose of a 12 cell horizontal reference into
vertical, but I need the tranpose function to lookup the line of the 12
cells
by finding a value in a vertical list. In other words, the value that
drives
it all is in a list. The 12 cells are to the right of the found cell. So
the vertical block of 12 cells (which would be the transposed values),
would
have to do the following:

find the lookup value
grab the 12 horizontal cells that start about 10 cells to the right of the
found value
transpose those 12 cells into vertical.

Thx for any help.

Hi Boris

Here's one way to do it.

Assuming the lookup value in A2, the lookup list in C2:C10 and
the 12-cells block in M2:X10

1. Select e.g. D1324
2. While selected enter this array formula
=TRANSPOSE(OFFSET(C2,MATCH(A2,C2:C10,0)-1,10,1,12))

The formula must be finished with <Shift><Ctrl><Enter>, also if
edited later.
If done correctly, Excel will display the formula in the formula bar
enclosed in braces { }. Don't enter these braces yourself.

Enter various numbers found in C2:C10 in A2 and D1324
will display the matching 12-cells range.