Stack Dynamic Columns into One

[noctash]

I am using this formula to stack columns:

=INDEX(DATA,1+INT((ROW(A1)-1)/COLUMNS(DATA)),MOD(ROW(A1)-1+COLUMNS(DATA),COLUMNS(DATA))+1)

'DATA' is a dynamic array referring to all data in the first 3 columns

=OFFSET('Sheet1'!$A$2,0,0,COUNTA'Sheet1'!$A:$A)-1,3)

The problem I am facing is that the first code requires me to drag down the cell until I hit an error to fill in all of my data. Is there a way I can automatically continue the formula until it hits an error, such that if more data is entered in the multiple columns, the single column will automatically adjust it's length? I have tried replacing A1 with A:A but it ends up returning the first value in 'DATA' indefinitely.

I am trying to avoid VBA for the same reason, I want to be able to copy/paste new values into 'DATA' and have the single column automatically adjust.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LET(Data,FILTER(A2:C10000,A2:A10000<>""),r,ROWS(Data),s,SEQUENCE(r*COLUMNS(Data),,0),INDEX(Data,MOD(s,r)+1,INT(s/r)+1))

No need for the named range

 

[noctash]

Hi & welcome to MrExcel.
How about

=LET(Data,FILTER(A2:C10000,A2:A10000<>""),r,ROWS(Data),s,SEQUENCE(r*COLUMNS(Data),,0),INDEX(Data,MOD(s,r)+1,INT(s/r)+1))

No need for the named range

This worked great! Thank you so much for your help and so quickly!

 

[Fluff]

You're welcome & thanks for the feedback.