using variable length named ranges problem - can you help ? concatenate 3 lists into one.

[tur13o]

I have 3 variable length lists of data that need to be concatenated in to
one column.



Presently the following formula works (This is typed in to A1 and copied
down the column using the drag handle so in cell A2 refs to C1,D1,E1 are
automatically changed to C2,D2,E2 and so on):



=IF(ROW()<=COUNTA(C:C),C1,IF(ROW()<=(COUNTA(C:C)+COUNTA(D)),OFFSET(D1,(COU
NTA(C:C)),0,1,1),IF(ROW()<=(COUNTA(C:C)+COUNTA(D)+COUNTA(E:E)),OFFSET(E1,-
(COUNTA(C:C)+COUNTA(D)),0,1,1),"")))



Result - a list of data in column A which is the list of data in column C
plus data in column D plus data in column E.



But I now want to make the lists of data in Cols C,D,E dynamic named ranges.
Creating the dynamic named ranges



andy:



=OFFSET('Input'!$C$1,0,0,COUNTA('Input'!$C:$C),1)



fred:



=OFFSET('Input'!$D$1,0,0,COUNTA('Input'!$D:$D),1)



bert:



=OFFSET('Input'!$D$1,0,0,COUNTA('Input'!$E:$E),1)





What I'd now like to do is use the named ranges in the 1st formula instead
of "C:C", "D", "E:E". And this is where I need some more brain power!
Because I can't figure it out.



The best I have discovered so far is: INDIRECT(CELL("address",andy))
Making my long formula :-



=IF(ROW()<=COUNTA(andy),INDIRECT(CELL("address",andy))....



- and it works in cell A1. But the drag formula from A1 to A2 can't
automatically increase the cell (as in C1 becomes C2 and so on) - so A2,A3.
all end up with the same data displayed in them.



Can anyone help ?