I’ve got an answer that doesn’t require coding.

- I’ll assume that you want to put the combined (concatenated) list
in Column
`D`

of the same sheet.
If you want it elsewhere, that’s a trivial change.
- My solution requires two “helper columns”;
for example, Columns
`E`

and `F`

on the same sheet.
If you want them elsewhere, that’s a trivial change.
- I assume that you will not be putting blanks
in the first three columns (i.e.,
**in** the data, before the last value),
and that you want those columns simply concatenated, and not sorted or deduped.
If I misunderstand, that’s a major change.
- I assume that every column has at least one value;
i.e., that
`A1`

, `B1`

and `C1`

are non-blank.
If that assumption is not valid,
this solution can (probably) be adapted for that.

## Solution:

- Enter
`=IF(E1<0, "", OFFSET($A$1, F1, E1))`

into cell `D1`

.
- Drag/fill cell
`D1`

down into `D2`

.
- Enter
`0`

into cells `E1`

and `F1`

.
- Enter
`=IF(E1<0, E1, IF(OFFSET($A$1, F1+1, E1)<>"", E1, IF(E1<2, E1+1, -1)))`

into cell `E2`

.
- Enter
`=IF(E1<0, 0, IF(OFFSET($A$1, F1+1, E1)<>"", F1+1, 0))`

into cell `F2`

.
- Select cells
`D2:F2`

and drag/fill down as far
as you expect the combined list ever to be.
(Of course you can always extend it later.)

Columns `E`

and `F`

contain
the (0-based) column and row number of the value in Column `D`

.
For example, `0,0`

is `A1`

, `0,2`

is `A3`

, `1,3`

is `B4`

, etc.
A negative value in Column `E`

indicates that
you are beyond the end of the data.
The formula in Column `D`

evaluates to blank if the `E`

value is negative;
otherwise, it uses the `OFFSET()`

function to retrieve
the value indexed by the `E`

and `F`

numbers.

The formulas in Columns `E`

and `F`

check whether
the previous `E`

value is negative,
and, if it is, set the next row to `-1,0`

—
so, once you’re past the end of the list, you’re past it,
and you don’t start tying yourself into knots.
Otherwise, if there is a next value in the current column,
we advance to that value (`E1,F1+1`

).
Otherwise, we go to the top of the next column —
except, if the current column is #2 (`C`

),
we advance to column #-1, because we’re at the end.

You can now add and delete values in Columns `A`

, `B`

and `C`

,
and the list in Column `D`

will update automatically.
Once you get this working, you can hide the helper columns.

thinkyou want to use`INDIRECT`

– Dave Dec 2 '16 at 12:36