In Google Sheets I am trying to use TRANSPOSE to list data from populated cells from Column range D-F directly after the Columns created by Transposing rows of data in range A-C.

Here is an image of what I'm trying to produce using fictional dataset: enter image description here Link to this sheet

I don't know how to get the Columns from the rows in the second range (D-F) to automatically display after the columns created by the data from the first range (A-C). Accomplishing this automatically, so that the Transposed regions will update dynamically with new columns when new rows of data are added to the original ranges, is the key.

Is this possible?

Do I need to use something like IMPORTRANGE or QUERY to accomplish this effect instead?

Any help would be appreciated!


1 Answer 1


You can make this a single dynamic Function like this:


By using the indirect function - we are able to only make the range as long as there is data to transpose.

enter image description here

  • Thank you for your help! That formula solves the problem elegantly! I really appreciate it!
    – Geoff
    Dec 1, 2016 at 23:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.