Is it possible to have a formula do something simila to a loop Macro?

I have a large spreadsheet and I want tpick up values in column out to the
right when I get the the bottom of a list.

So if Column A has values down to row 100 and columns D-G have values
corresponding to Column A.

In another tab I want to bring in the values in Column D, then when I get
the bottom of A or D because they are blank then start at the top of column E
values and so on through to the bottom of G. So that this second tab will
have 400 values in it.

The reason I can't just hard code is because the length of Column A might
grow.

Can this be done without a macro? I am creating jnl and could create 4
separate jnls but was hoping to keept it to just one.

Thanks
Rick