Questions

Excel 2003

Tags:
+
0 Votes
Locked

Excel 2003

Quim.JC
Hi to all,

So this is the problem:
i've got a Transpose formula that transposes 8 lines to 8 columns. i need to repeat the formula each 8 rows.. like this:

=TRANSPOSE(A9:A16)
=TRANSPOSE(A17:A24)
=TRANSPOSE(A25:A32)

and so on.. but with the click and drag Excel increases +1 in the A's.. i need it to increase +8.

something like this:
=TRANSPOSE(A17(+8):A24(+8))
=TRANSPOSE(A25(+8):A32(+8))


is there any way to do this? :)

thank you for your help!
  • +
    0 Votes
    keith.bibbee

    I tried this and I can't figure out a way to get the Transpose to copy correctly either.

    However, assuming all you want to do is put your data in a table of 8 columns by many rows, instead of Transpose, you can use the Offset function. In your example, assuming your data starts at cell A9 and goes down A10, A11, etc. and assume you want your 8 pieces of data transposed to rows with first row starting C9, D9,.. etc, second row starting C10, C11,... type in the following formulas in the cells indicated.

    C9: =OFFSET(A$9,K9,0)
    D9: =OFFSET(A$10,K9,0)
    E9: =OFFSET(A$11,K9,0)
    F9: =OFFSET(A$12,K9,0)
    G9: =OFFSET(A$13,K9,0)
    H9: =OFFSET(A$14,K9,0)
    I9: =OFFSET(A$15,K9,0)
    J9: =OFFSET(A$16,K9,0)
    K9: 0
    K10: 8

    Next select area C9 thru J9 (not including K9), grab the corner and drag down 1 line to autofill row 10. Now you can select the area C9 thru K10, grab the corner and drag down as many lines as you need. You can also hide column K if that helps.

    I hope this will accomplish what you need.

    Keith

  • +
    0 Votes
    keith.bibbee

    I tried this and I can't figure out a way to get the Transpose to copy correctly either.

    However, assuming all you want to do is put your data in a table of 8 columns by many rows, instead of Transpose, you can use the Offset function. In your example, assuming your data starts at cell A9 and goes down A10, A11, etc. and assume you want your 8 pieces of data transposed to rows with first row starting C9, D9,.. etc, second row starting C10, C11,... type in the following formulas in the cells indicated.

    C9: =OFFSET(A$9,K9,0)
    D9: =OFFSET(A$10,K9,0)
    E9: =OFFSET(A$11,K9,0)
    F9: =OFFSET(A$12,K9,0)
    G9: =OFFSET(A$13,K9,0)
    H9: =OFFSET(A$14,K9,0)
    I9: =OFFSET(A$15,K9,0)
    J9: =OFFSET(A$16,K9,0)
    K9: 0
    K10: 8

    Next select area C9 thru J9 (not including K9), grab the corner and drag down 1 line to autofill row 10. Now you can select the area C9 thru K10, grab the corner and drag down as many lines as you need. You can also hide column K if that helps.

    I hope this will accomplish what you need.

    Keith