How would I reference cells in order while moving down several cells in a formula on a separate shee-CodePudding

I have been at this for hours and it's kicking me. I'm trying to build a log for someone, and I have a sheet with standard data in table format. I need the next sheet to look a certain way so that it can be exported to PDF and continue looking like the log always has - which means that it will not be a standard table.

In the Log sheet, data is all on one row, in the PrintSheet the cell references will be placed in three rows, with a gap fourth row. Obviously, when you paste formulas in Excel, it picks the row you're in, vs the next row down in the referenced sheet. I've included the formulas that "work" in blue in the image for reference, but that would involve manually subtracting 3 (or 4 depending on which one I'm doing) to each formula (Formula for reference -- =Log!$A$1&": "&INDIRECT("'log'!A"&ROW()-3).

Is there a way to dynamically write this formula so it can just be copy/pasted every 4th row when they need more in the PrintSheet? Is it possible I need to be using an array formula (that is an area of Excel that I am deeply lacking in)?

CodePudding user response：

Use a bit of maths on the row number and pull the formula down as required:

=CHOOSE(MOD(ROW()-2,4) 1,Log!$A$1&":"&INDEX(Log!A:A,QUOTIENT(ROW()-2,4) 2),Log!$D$1&":"&INDEX(Log!D:D,QUOTIENT(ROW()-2,4) 2),"","")

Log:

PrintSheet:

If you have Excel 365, you can do it using the same method but as a spill formula:

=LET(logRows,COUNTA(Log!A:A)-1,
seq,SEQUENCE(logRows*4,1,0),
CHOOSE(MOD(seq,4) 1,Log!$A$1&":"&INDEX(Log!A:A,QUOTIENT(seq,4) 2),
Log!$D$1&":"&INDEX(Log!D:D,QUOTIENT(seq,4) 2),"",""))

In this case it counts the number of rows in the log so will expand as you add more rows.

The date and time can be done in a similar way.