Home > Software engineering >  Repeat blocks of columns and rows N times dynamically in Google Sheets
Repeat blocks of columns and rows N times dynamically in Google Sheets

Time:06-07

I am currently working on a scheduling spreadsheet in Google Sheets and am looking for a way to repeat a block of cells with dynamic values a fixed number of times. I tried using REPT and SPLIT, but these just repeat the text value of the cell and not the formulas. Referring to the image below, I need to repeat the outlined block, but preserve the dynamic values specified in the cell formulas (in this case, the next iteration would be titled "WK 3" and the dates would begin at 6/13).

I am quite new to spreadsheets, and any help would be greatly appreciated!

example image

CodePudding user response:

try:

=ARRAYFORMULA({
 "WK "&ISOWEEKNUM(SEQUENCE(1, 7*A2, "2022-05-30"))-ISOWEEKNUM(TODAY()) 1;
 TEXT(SEQUENCE(1, 7*A2, "2022-05-30"), "ddd");
 TEXT(SEQUENCE(1, 7*A2, "2022-05-30"), "m/d")})

enter image description here

or shorter:

=ARRAYFORMULA({
 "WK "&ISOWEEKNUM(SEQUENCE(1, 7*A2, "2022-05-30"))-ISOWEEKNUM(TODAY()) 1;
 TEXT(SEQUENCE(1, 7*A2, "2022-05-30"), {"ddd"; "m/d"})})

demo sheet

  • Related