Repeat a dynamically sized array in one column

[dr_gzus]

Hi. I'm not sure this is possible but here goes. And thanks in advance for any insight, even if that insight is "you can't".

I have a number of dynamically sized, named arrays that I want to repeat in a column. For instance, the baseline array might have four values in a column (representing 4 weeks of a repeating 4-week cycle), but if the end user decides the cycle is 5 weeks, the array expands to 5 cells.

I want to repeat the array in a single column. Thus if the cycle is 4 weeks, the same 4-value sequence repeats every four cells in the column. Then if the array expands to 5 cycles... you get the idea.

Is there a formulaic way to do this? Trying to avoid VBA but I'll do that if I must (and can probably figure that out on my own but if you have some code on hand, well, I won't say no to it

 

[dr_gzus]

bump

 

[dr_gzus]

I figured this out in case someone finds this thread on google in the future. I ditched the plan to use the named range, and instead created a parameter that dictates the number of rows in the cycle (i.e. 3 or 4 or 5...). Then used this formula to dynamically populate the column with the array of specified size:

=IF(MOD(x-1,y)>0,OFFSET(z,MOD(x-1,y),0),z))

Where
- x is the week number (my model starts at week 1 but for the mod() formula to work right I need it to read as starting at week 0, hence the subtraction of 1 from x)
- y is the number of weeks (rows) in a cycle
- z is the first cell in the array