Adding Second Row of Transposed Data ONLY After End of Data From First Row

Question

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: 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!

Answer 1

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.