Append cells or values to an existing array of cells in formula

[dave2018]

Hi,

I have an array formula that ends off with {"1";"2";"3","4"}

I need a formula trick that can add cells or text to this column, so that when i evaluate it, it will become {"1";"2";"3","4","5","6","7"}

The latter numbers will be coming from cells or hard-coded values. I need both ways (which shouldn't make a difference anyways).

I've tried the choose({1;2},{array1},{array2}) method, but it's not working like i need it. it need to be robust to expand to any size i feed it.

(probably will need indirect in the beginning)

Hope i'm clear enough!

Thanks

 

[Rick Rothstein]

i will still try to develop my formula above.

As I said, "if you should you change your mind".

 

[dave2018]

Thanks so much.

 

[Marcelo Branco]

yes. i will keep that code for refrence, but i really try to stay away from udf when possible. just my 'ocd'. thanks though for all your help .

i will still try to develop my formula above.

Ok. Another possible formula (post 17)

Create an array constant (assumes data in Sheet1)
Formulas > Names Manager > New
Name: Vector
Refers to:=ROW(Sheet1!$A$1:$A$100)-ROW(Sheet1!$A$1)+1

Then in E1 this array formula
=TEXTJOIN(",",1,IFERROR(IF(Vector<=ROWS(A1:A8),INDEX(A1:A8,N(IF(1,Vector))),INDEX(C1:C2,N(IF(1,Vector))-ROWS(A1:A8))),""))
Ctrl+Shift+Enter

M.