Excel 2003

By 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:


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:

is there any way to do this? :)

thank you for your help!

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Solution without Transpose

by keith.bibbee In reply to Excel 2003

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.


Related Discussions

Related Forums