PDA

View Full Version : Transpose Function



asingh
11-10-2008, 10:54 PM
Hi,

I have the following transpose function and it is working perfectly..

=IF(C3="","",TRANSPOSE(INDIRECT("$B$"&C3-3&":$B$"&C3)))

Where in this case C3 = 9...and I have selected the above function on the range D3:G3 and arrayed it. It transposes the contents from B6:B9 onto D3:G3.

Now when I try to compute the values of C3 within this tranpose function, using the ROW() function within it, it is just returning the first element in all the cells.

=T(TRANSPOSE(INDIRECT("$B$"&IF((ROW()*4)-8>cnt_rspnts,"",ROW()*4-3)-3&":$B$"&C2)))

When I evaluate the formula, using F9 on each segment the dynamic generated range is composed within an array... {"$B$6:$B$9"}, when I INDIRECT this only the first element from C3 is returned in D3,E3,F3,G3. cnt_rspnts is a named range, used for evaluation.

So my dynamic range, which is fed into the INDIRECT function is getting generated correctly, but each element is not being returned. I think because I am using ROW(), it is throwing the logic off.

Help would be appreciated..

thanks and regards,

asingh