Excel/Google Sheets Offset and Transpose formula question

1
votes

I have a query regarding the OFFSET and TRANSPOSE formulas. I have the following two sheets set up in Google Sheets (originally in Excel):

First sheet: First Sheet

Formula in cell B2: =transpose('Form responses 1'!A2:BB2) (original)

=transpose(offset('Form responses 1'!A$2:BB$2,COLUMNS($A$2:A2)-1,0)) (current)

Formula in cell C2: =transpose('Form responses 1'!A3:BB3)

Formula in cell D2: =transpose('Form responses 1'!A4:BB4)

I would like the references to increase by row (vertically down) rather than by column (horizontally right) when I drag across to copy the formula.

Second sheet: Second Sheet

As you can see in the first sheet, I am trying to TRANSPOSE data from the second sheet using said formula. However when I go to drag the formula across (horizontally) it references the column when instead I need it to reference the row (if I drag it downwards it works fine but that is not what I need in this particular case).

I understand I need to implement a OFFSET function, something along the lines of: =transpose(offset('Form responses 1'!A$2:BB$2,COLUMNS($A$2:A2)-1,0))

I am unsure of what the last part would need to be, COLUMNS($A$2:A2)-1,0, what should I change this to to get the desire result?

If I have not explained thoroughly enough please let me know, thanks.

1
excelgoogle-sheetsexcel-formulaoffsettranspose
try out =transpose(query('Form responses 1'!A:BB, "Select *",-1) this should work I guess? if you share a sheet with dummy & desired content then we can help. the -1 removes headers. Also if you need to query multiple forms let me know and I'll write an answer and adapt the formula. – Umar.H
Thank you for the solution I play around with it, albeit, I have implemented Ed's suggestion below successfully. I appreciate your time. – Env67
no poblemo! if you need to query multiple forms you can pass them into the first part of the query argument as an array so =query({'Form 1'!A:C;'Form 2'!A:C}, "Select *") you can also append import range at the start if forms are held on different sheets. Good luck! – Umar.H

1 Answers

1
votes

Use arrayformula and you won't have to drag anything. Try:

=arrayformula(transpose('Form responses 1'!A$1:BB$4))