Google Sheets Transposed Data – Adding Second Row of Transposed Data Only After End of First Row

google sheets

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!

Best Answer

You can make this a single dynamic Function like this:

={TRANSPOSE(indirect("A3:C"&counta(B:B))),TRANSPOSE(indirect("D3:F"&counta(E1:E)+1))}

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

Related Topic