No VBA method to create a dynamic array constant

Dear All:

I am looking for a way (without VBA) to create a dynamic array constant which has the value of {1,1,1;2,2,0;3,0,0} in column 3 and {1,1,1,1;2,2,2,0;3,3,0,0;4,0,0,0} in column 4.. and so on and so forth

This is where I have reached so far:
I was able to figure out that an array formula =CHOOSE(TRANSPOSE(A1:C1),{1,1,1},{2,2,0},{3,0,0}) where A1=1,B1=2,C1=3 gives me the solution and =A1*--(A1:A3<D1) gives me the value of {1,1,1}.. but when I try combining the above two into a single forumla as =CHOOSE(TRANSPOSE(A1:C1),A1:C1*--(OFFSET(A1:C1,0,0,1,C1)<D1)).. i am returned the value of {1,2,3;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!}....

I cant seem to figure out how to get the above formula to work or some other way to get the constant {1,1,1;2,2,0;3,0,0}.

Please do help.

Thanks
Akshay

couldbe, but its what its called... a array constant.... ! so you dont think you can help? you are here just to poke fun at the vagaries of the excel naming systems?

TheN, thats exactly what i Got. a diagonal and zero below the diagonal. but could not figure out how to get the solution. Could you please explain how to do the vba solution with a non-macro book as I need to share the model with others who dont like to open macro enabled books for obvious reason.

Thank you in advance!

Akshay

I don't know how to do VBA that well yet (not familiar enough with syntax), but I can think of how to do it in programming language, so it is definitely possible with VBA.

Someone could write a UDF that would work off the row numbers to create the desired arrays. From there, you just copy them into a different workbook and save them for reference. That's my suggestion, so I suggest you update your title as to not scare away the appropriate people. Basically you'd create a "cheat sheet" using a macro then just use it in your non-macro book, if that makes sense.

Never mind! I was able to figure it out eventually! took some complex trial and errors but nothing feels better than being able to solve a nagging array!

Thanks shg.. I was looking for a non vba approach and as i mentioned earlier, i was able to figure out a way which turned out to be very simple. But thank you for taking the time to make this function for me.

Best regards,
Akshay