For instance, fill Sheet 2 Cells E11, F11, G11 etc with the values in Sheet 1 G25, G26, G27 etc.
Ideally, I would like to copy G25 to E11 then drag the the bottom righr corner of E11 to fill the following cells in row E.
The technical term for converting columns to rows (or reverse) is transposition.
The formula that can do it in one go would be one with array output. Such formulas need to lock a fix output range. =TRANSPOSE($Sheet1.G25:G34) entered into cell Sheet2.E11 With Ctrl+Shift+Enter to force array-evaluation would do, but the length of the returned stripe would be a constant (10 in this case) then, and scaling it to a different range would be a bit unhandy.
The other way to do it is to create the results per target cell one by one using OFFSET():
enter =OFFSET($Sheet1.$G$25;COLUMN(E11) - COLUMN($E11);0) into Sheet2.E11, and fill it to the right for as many cells as needed.
Explanation:
The second argument of OFFSET() gives the row-offset.
COLUMN(E11) - COLUMN($E11) is 0 (in cell E11). The expression increases by one in the cells filled to the right of E11 because the column reference of the first part will be respectively adapted (to F11, G11, and so on) during the filling.
See attached demo:
disask102878specialTransposition.ods (10.0 KB)
There you find also a variant of the second solution avoiding to get returned the valoe 0 for blank source cells.
1 Like
Hello @Lupp
Thank you for your prompt and very helpful answer.
I have used the Function Wizard to select the TRANSPOSE function.
The process was simple and created the result that I wanted.
I have not yet tried the OFFSET method but I will later.
Thanks again
Geoff