formula to transpose rows to columns

[purlo]

I need from 'sheet1!' transpose rows into column in 'sheet2!'. Transpose function is not good for me. i need index or soemthing like that. here is the example what i would like to accomplish: question 2.xlsx

[daffodil11]

=INDEX(Sheet1!$A$1:$G$1,SMALL(IF(Sheet1!$A$1:$G$1<>"",COLUMN(Sheet1!$A$1:$G$1)),ROW(A1)))

Array formula confirmed with Ctrl+Shift+Enter

and copied down

[purlo]

Delete this post

[purlo]

Thank you, thats wonderful.

how the formula that does not skip empty gaps looks like?

[daffodil11]

It's kind of complicated. I will change the references to their values and use bold to show you what's happening.

A3:

=INDEX(Sheet1!$A$1:$G$1,SMALL(IF(Sheet1!$A$1:$G$1<>"",COLUMN(Sheet1!$A$1:$G$1)),ROW(A3)))
=INDEX(Sheet1!$A$1:$G$1,SMALL(IF({"age",17,0,19,0,0,20}<>"",COLUMN(Sheet1!$A$1:$G$1)),ROW(A3)))
=INDEX(Sheet1!$A$1:$G$1,SMALL(IF({TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE},COLUMN(Sheet1!$A$1:$G$1)),ROW(A3)))
=INDEX(Sheet1!$A$1:$G$1,SMALL(IF({TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE},{1,2,3,4,5,6,7}),ROW(A3)))
=INDEX(Sheet1!$A$1:$G$1,SMALL({1,2,FALSE,4,FALSE,FALSE,7},ROW(A3)))
=INDEX(Sheet1!$A$1:$G$1,SMALL({1,2,FALSE,4,FALSE,FALSE,7},{3}))
=INDEX(Sheet1!$A$1:$G$1,{4})
=INDEX({"age",17,0,19,0,0,20},{4})
=19

[daffodil11]

Also, illustrated:

[daffodil11]

Not skipping gaps:

=INDEX(range,0,ROW(A1))

and drag down

[purlo]

can u upload file? when i enter this formula it shows error..

[daffodil11]

here is the attachment