What's wrong with this formula?

  • Thread starter Thread starter Bodster
  • Start date Start date
B

Bodster

=offset('sheet1'!$a$1,int((column()-1/5),mod(column()-1,5))

It's to transform a sheet of 10 rows by 5 columns of data into 1 row of 50
columns. Please help.

B
 
One way ..

Assuming the source grid is in Sheet1, in A1:E10

To extract into 1 row of 50 columns (1R x 50C) :

Put in any starting cell in a new sheet:

=OFFSET(Sheet1!$A$1,INT((COLUMN(A1)-1)/COLUMNS(Sheet1!$A$1:$E$10)),MOD(COLUM
N(A1)-1,COLUMNS(Sheet1!$A$1:$E$10)))

Copy across a total of 50 columns

---

To extract into 1 column of 50 rows (50R x 1C):

Put in any starting cell in a new sheet:

=OFFSET(Sheet1!$A$1,INT((ROW(A1)-1)/COLUMNS(Sheet1!$A$1:$E$10)),MOD(ROW(A1)-
1,COLUMNS(Sheet1!$A$1:$E$10)))

Copy down a total of 50 rows
 
Back
Top