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.

 

[TrashmanL]

Wanted to add an FYI: In march 2022, Microsoft added their own MAKEARRAY function. But, even more to the point, there are now VSTACK and HSTACK functions which should do exactly what you want to do and in a much easier way. See the article here: March 2022 Excel new array functions

You will need Excel for Microsoft 365 version 2208 or later to use these functions.