what you suggest is well beyond A formula. formulas return values to the cell
in which they reside. if you excect to have 400 values in your second sheet,
then it would be expected that you have 400 formulas.
since you are vague about your data and only mentioned that column a values
match columns d-g value, this might suggest a lookup or match formula but
without fruther details, i would hesitate to suggest anything specilfic or
even try.

there is no real problem with a varying length to column a. you can find the
last cell of column a with this line each time a macro is run.
dim c as long
c = cells(rows.count,"A").end(xlup).row
msgbox c
i would guess that a macro would be your best bet for you project. formulas
just wont do what you ask.

Regards
FSt1
"RD" wrote:

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